首页 IT程序员内容详情

SQL中的ON和WHERE的区别

2021-07-05 41479 zpzpzp12270910

本文主要介绍SQL语句中ON和WHERE的区别。


1 概述

当我们使用连接操作,关联两张或多张表来返回记录时,数据库都会生成一张临时表,最后将这张临时表返回给用户。


以 LEFT JOIN 为例:在使用 LEFT JOIN 时,ON 和 WHERE 过滤条件的区别如下:


ON 条件是在生成临时表时使用的条件,它不管 ON 中的条件是否为真,都会返回左边表中的记录;

WHERE 条件是在临时表已经生成后,对临时表进行的过滤条件。因为此时已经没有 LEFT JOIN 的含义(必须返回左侧表的记录)了,所以如果 WHERE 条件不为真的记录就会被过滤掉。

2 示例

我们对此文中的两张表 roles 和 mount_info 进行操作,来介绍 ON 和 WHERE 之间的区别。


2.1 ON+WHERE过滤条件

ON+WHERE过滤条件语句如下:


SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";

上述SQL语句的运行结果如下:


mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";

+---------+------------+----------+----------+------------+---------+

| role_id | occupation | camp     | mount_id | mount_name | role_id |

+---------+------------+----------+----------+------------+---------+

|       1 | warrior    | alliance |        2 | sheep      |       1 |

+---------+------------+----------+----------+------------+---------+

1 row in set (0.00 sec)

 

mysql> 

分析上述SQL语句的执行过程,如下:


1. 首先,根据ON过滤条件“roles.role_id = mount_info.role_id”(相当于去掉后面的WHERE过滤条件),生成中间表,如下:


mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id);

+---------+------------+----------+----------+------------+---------+

| role_id | occupation | camp     | mount_id | mount_name | role_id |

+---------+------------+----------+----------+------------+---------+

|       1 | warrior    | alliance |        1 | horse      |       1 |

|       1 | warrior    | alliance |        2 | sheep      |       1 |

|       2 | paladin    | alliance |     NULL | NULL       |    NULL |

|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |

+---------+------------+----------+----------+------------+---------+

4 rows in set (0.00 sec)

 

mysql> 

2. 然后,针对上面生成的中间表,再根据WHERE过滤条件“mount_info.mount_name="sheep"”,产生最终查询结果,如下:


mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id) WHERE mount_info.mount_name="sheep";

+---------+------------+----------+----------+------------+---------+

| role_id | occupation | camp     | mount_id | mount_name | role_id |

+---------+------------+----------+----------+------------+---------+

|       1 | warrior    | alliance |        2 | sheep      |       1 |

+---------+------------+----------+----------+------------+---------+

1 row in set (0.00 sec)

2.2 ON过滤条件

ON过滤条件语句如下:


SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep");

上述SQL语句的运行结果如下:


mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep");

+---------+------------+----------+----------+------------+---------+

| role_id | occupation | camp     | mount_id | mount_name | role_id |

+---------+------------+----------+----------+------------+---------+

|       1 | warrior    | alliance |        2 | sheep      |       1 |

|       2 | paladin    | alliance |     NULL | NULL       |    NULL |

|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |

+---------+------------+----------+----------+------------+---------+

3 rows in set (0.01 sec)

 

mysql> 

分析上述SQL语句的执行过程,如下:


根据ON过滤条件“roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep"”,生成中间表,即使ON过滤条件不为真,也会返回左表中的所有记录。


3 总结

对比下面两条ON过滤条件语句及其运行结果:


mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id);

+---------+------------+----------+----------+------------+---------+

| role_id | occupation | camp     | mount_id | mount_name | role_id |

+---------+------------+----------+----------+------------+---------+

|       1 | warrior    | alliance |        1 | horse      |       1 |

|       1 | warrior    | alliance |        2 | sheep      |       1 |

|       2 | paladin    | alliance |     NULL | NULL       |    NULL |

|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |

+---------+------------+----------+----------+------------+---------+

4 rows in set (0.01 sec)

 

mysql> SELECT * FROM roles LEFT JOIN mount_info ON (roles.role_id = mount_info.role_id AND mount_info.mount_name = "sheep");

+---------+------------+----------+----------+------------+---------+

| role_id | occupation | camp     | mount_id | mount_name | role_id |

+---------+------------+----------+----------+------------+---------+

|       1 | warrior    | alliance |        2 | sheep      |       1 |

|       2 | paladin    | alliance |     NULL | NULL       |    NULL |

|       3 | rogue      | Horde    |     NULL | NULL       |    NULL |

+---------+------------+----------+----------+------------+---------+

3 rows in set (0.01 sec)

 

mysql> 

能够发现ON过滤条件在 LEFT JOIN 模式下,相当于只增加了对于右侧表的过滤条件,而左侧表的内容,总是会全部返回的。


出现上述情况的原因在于 LEFT JOIN(以及 RIGHT JOIN、FULL JOIN )的特殊性,不管 ON 条件是否为真,数据库都会返回左侧(或右侧、左右两侧)表中的全部记录。 由于 INNER JOIN 没这样的特殊性,所以过滤条件放在 ON 中或 WHERE 中,其返回的结果是一样的。


 

————————————————

版权声明:本文为CSDN博主「liitdar」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。

原文链接:https://blog.csdn.net/liitdar/article/details/80817957


相关标签: # java

 暂无评论,快来抢沙发吧~

发布评论