Mysql 7种join连接方式深入分析

最新推荐文章于 2025-06-28 19:57:33 发布

贤冰

最新推荐文章于 2025-06-28 19:57:33 发布

阅读量2.3k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Mysql 文章标签： Mysql left join 左连接右连接 right join

本文链接：https://blog.youkuaiyun.com/flyfreelyit/article/details/80136459

本文深入剖析了MySQL中的JOIN连接类型，包括LEFT JOIN、RIGHT JOIN和全连接（FULL JOIN），详细解释了它们的定义和应用场景。通过实例演示了不同类型的JOIN操作，并探讨了外连接消除的条件和优化技巧，以及连接消除在特定情况下的应用，旨在帮助读者更好地理解和使用MySQL的JOIN操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一，定义：

1）LEFT JOIN / LEFT OUTER JOIN：左外连接

左向外连接的结果集包括：LEFT OUTER子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2）RIGHT JOIN / RIGHT OUTER JOIN：右外连接

右向外连接是左向外联接的反向连接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。

3）FULL JOIN / FULL OUTER JOIN：全外连接

全外连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值

二，演示：

1，内连接

SELECT <list> FROM TABLEA A INNER JOIN TABLEB B ON A.key=B.key

2，全外连接 Mysql暂不支持

SELECT <list> FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.key=B.key

3，全外连接-差集 Mysql暂不支持

SELECT <list> FROM TABLEA A FULL OUTER JOIN TABLEB B ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL

4，左(外)连接

SELECT <list> FROM TABLEA A LEFT JOIN TABLEB B ON A.key=B.key

5，左(外)连接-左表无连接部分

SELECT <list> FROM TABLEA A LEFT JOIN TABLEB B ON A.key=B.key WHERE B.key IS NULL

6，右(外)连接

SELECT <list> FROM TABLEA A RIGHT JOIN TABLEB B ON A.key=B.key

7，右(外)连接-右表无连接部分

SELECT <list> FROM TABLEA A RIGHT JOIN TABLEB B ON A.key=B.key WHERE A.key IS NULL

三，外连接消除：

把外连接变为内连接

A OUTER JOIN B

变形为

A JOIN B

外连接消除的意义：

1 查询优化器在处理外连接操作时所需执行的操作和时间多于内连接

2 外连接消除后，优化器在选择多表连接顺序时，可以有更多更灵活的选择，从而可以选择更好的表连接顺序，加快查询执行的速度

3 表的一些连接算法（如块嵌套连接和索引循环连接等）在将规模小的或筛选条件最严格的表作为“外表”（放在连接顺序的最前面，是多层循环体的外循环层），可以减少不必要的I/O开销，能加快算法执行的速度

外连接消除的条件：

WHERE子句中的条件满足“空值拒绝” （又称为“reject-NULL”条件）。

WHERE条件可以保证从结果中排除外连接右侧（右表）生成的值为NULL的行（即条件确保应用在右表带有空值的列对象上时，条件不满足，条件的结果值为FLASE或UNKONOWEN，这样右表就不会有值为NULL的行生成），所以能使该查询在语义上等效于内连接。

explain

SELECT * FROM X LEFT JOIN Y ON (X.X_num=Y.Y_num)

WHERE Y.Y_num IS NOT NULL;

Mysql优化器对外连接的优化处理：

例1

explain extended select * from a left join b on a.id=b.uid where a.id=b.uid;

优化后：

select `test`.`a`.`id` AS `id`,`test`.`a`.`name` AS `name`,`test`.`a`.`age` AS `age`,`test`.`b`.`uid` AS `uid`,`test`.`b`.`score` AS `score`,`test`.`b`.`class` AS `class` from `test`.`a` join `test`.`b` where (`test`.`b`.`uid` = `test`.`a`.`id`)

例2

explain extended select * from a left join b on a.id=b.uid where b.uid is not null;

优化后：

select `test`.`a`.`id` AS `id`,`test`.`a`.`name` AS `name`,`test`.`a`.`age` AS `age`,`test`.`b`.`uid` AS `uid`,`test`.`b`.`score` AS `score`,`test`.`b`.`class` AS `class` from `test`.`a`