【转】MySQL 多表Join条件在ON AND 和 Where的写法差异
【转】MySQL 多表Join条件在ON AND 和 Where的写法差异
保持热爱奔赴山海
发布于 2024-11-26 18:18:42
发布于 2024-11-26 18:18:42
下文内容来自 恩墨 崔老师。
在MySQL中，多表Join是一种常见的操作，它允许从多个表中根据相关联的列，来组合提取数据。MySQL中多表关联也是支持，多种方式.。比如内连接，左链接，右链接，笛卡尔积等方式。特别是在左右链接下，不同的写法颠覆了对SQL语句处理的理解。
示例分析： 两张表s1 和 s2，LEFT JOIN下s1.name IN(‘a’,‘c’)条件使用在AND 和 WHERE 下得到的结果集不一样。
mysql> CREATE TABLE s1(id int ,name varchar(20));
CREATE TABLE s2(id int);
INSERT INTO s1 VALUES(1,'a'),(2,'b'),(3,'c');
INSERT INTO s2 VALUES(1),(2);
#第一种方式：LEFT JOIN ON AND关联
mysql> SELECT s1.id,s1.name,s2.id
FROM s1 LEFT JOIN s2
ON s1.id=s2.id AND s1.name IN('a','c');
#第二种方式：LEFT JOIN ON WHERE关联
mysql> SELECT s1.id,s1.name,s2.id
FROM s1 LEFT JOIN s2
ON s1.id=s2.id WHERE s1.name IN('a','c');在使用LEFT JOIN ON AND 和 LEFT JOIN ON WHERE时，前者得到3个返回值，后者得到2个返回值。
按照一般使用场景，本意是第二种结果（WHERE），但往往一不注意就会写成第一种方式。通过EXPLAIN执行 SHOW WARNINGS，Extra里可以看出最终执行计划的语句.
第一种方式：ON AND把条件带到生成的临时表
mysql> EXPLAIN SELECT s1.id,s1.name,s2.id FROM s1 LEFT JOIN s2 ON s1.id=s2.id
AND s1.name IN('a','c');
+----+-------------+-------+------------+------+---------------------------------------------------
| id | select_type | table | partitions | type | Extra | +----+-------------+-------+------------+------+---------------------------------------------------
| 1 | SIMPLE | s1 | NULL | ALL | NULL |
| 1 | SIMPLE | s2 | NULL | ALL | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------------------------------------------
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------+
| Level | Code | Message | +-------+------+----------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `demo`.`s1`.`id` AS `id`,`demo`.`s1`.`name` AS `name`,`demo`.`s2`.`id` AS `id`
from `demo`.`s1` left join `demo`.`s2` on(
( (`demo`.`s2`.`id` = `demo`.`s1`.`id`) and (`demo`.`s1`.`name` in ('a','c')) )
)
where true |
+-------+------+----------------------------------------------------------------------------------+第二种方式：JOIN完成之后，WHERE最后再进行一次筛选。
mysql> EXPLAIN SELECT s1.id,s1.name,s2.id FROM s1 LEFT JOIN s2 ON s1.id=s2.id
WHERE s1.name IN('a','c');
+----+-------------+-------+------------+------+---------------------------------------------------
| id | select_type | table | partitions | type | Extra | +----+-------------+-------+------------+------+---------------------------------------------------
| 1 | SIMPLE | s1 | NULL | ALL | Using where |
| 1 | SIMPLE | s2 | NULL | ALL | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------------------------------------------
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------+
| Level | Code | Message | +-------+------+----------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `demo`.`s1`.`id` AS `id`,`demo`.`s1`.`name` AS `name`,`demo`.`s2`.`id` AS `id`
from `demo`.`s1` left join `demo`.`s2` on( (`demo`.`s2`.`id` = `demo`.`s1`.`id`) ) where (`demo`.`s1`.`name` in ('a','c')) |
+-------+------+----------------------------------------------------------------------------------+总结
在LEFT（RIGHT）JOIN场景下，可以总结如下： 1.ON条件是在生成临时表时使用的条件，它不管ON中的条件是否为真，都会返回左边表中的记录。 AND 的条件只在右表中进行是否为真的条件显示
2. WHERE条件是在临时表生成好后，再对临时表进行过滤的条件。 这时已经没有LEFT JOIN的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。
在MySQL当中，除了INNER JOIN外，使用JOIN类型时，一定要把ON 和 WHERE条件正确使用。
本文系转载，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
本文系转载，前往查看
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
