MySQL学习笔记（十）内外连接及多表连接

关注

原创最新推荐文章于 2026-06-16 14:12:57 发布 893 阅读 AI 写同款· GEO 优化 ›

【MySQL】复合查询+表的内外连接可以看我们目前有什么，目前我们有部门号和部门平均工资，在我看来这就是一张表，然后我手里还有一张员工表，所以是不是可以把两张表做笛卡尔积，做完之后每个员工它所在那个部门已经所在部门的平均工资，在新的表中不就有了吗。这个笛卡尔积将两张表的信息做了穷举，可是做完笛卡尔积有些信息是有意义的，有些一看就无意义的的，就如一个员工所属部门它目前并不需要和它不是同一个部门号的平均工资做对比，如果将来需要再说。如果多表查询，我们想让左侧的表完全显示不要过任何过滤筛选，如果和右侧的表配不上，让右侧的都为空也可以。阅读详情

1. 内外连接的区别

(1) 内连接：显示左表及右表符合连接条件的记录

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
只会显示23条记录，而缺少了一条记录，因为商品表中cates_id为12的表与种类表不符合连接条件。

(2) 左外连接：显示左表的全部记录及右表符合连接条件的记录。

//可以看出有第24条记录，但是其cate_id为NULL

SELECT goods_id,goods_name,cate_name FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

(3) 右外连接：显示右表的全部记录及左表符合连接条件的记录

SELECT goods_id,goods_name,cate_name FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

2. 多表连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g

INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id

INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

3. 连接的几点说明

外联接：A LEFT JOIN B join_condition

       数据表B的结果依赖数据表A。数据表A的结果集根据左连接条件所依赖所有数据表（B表除外）。左连接条件决定如何检索数据表B（在没有指定WHERE条件的情况下）。如果数据表A的某条记录符合WHERE调价，但是在数据表B不存在符合连接条件的记录，将生成一个所有列为空的额外的B行。

        如果在使用内连接查找的记录在连接数据表中不存在，并且在WHERE子句中尝试以下操作：col_name IS NULL时，如果col_name被定义为NOT NULL，MySQL将在找到符合连接条件的记录后停止搜索更多的行。

4. 无限级分类表设计

自身连接：同一个数据表对其自身进行连接。

//创建商品种类表，第三个字段存储表自身的type_id字段

CREATE TABLE tdb_goods_types(
     type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
     type_name VARCHAR(20) NOT NULL,
     parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

//插入记录

INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);
INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);
INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);
INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);
INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

//显示种类表中的记录

SELECT * FROM tdb_goods_types;

//现在想要查找表中子类的父类，如子类大家电和生活电器的父类时家用电器，以此类推

//需要用到自身连接

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

//同样也可以查到父类下的子类

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON p.type_id = s.parent_id;

//现在想要获取父类下子类的个数，因此需要对父类进行分类

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON p.type_id = s.parent_id GROUP BY p.type_name ORDER BY p.type_id;

SELECT p.type_id,p.type_name,COUNT(s.type_name) AS child_type FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON p.type_id = s.parent_id GROUP BY p.type_name ORDER BY p.type_id;