sql中的过滤条件放在on和where的区别

公众号-利志分享

发布于 2022-04-25 08:45:11

3.8K00

代码可运行

文章被收录于专栏：利志分享利志分享

运行总次数：0

代码可运行

最近遇到相关业务，想揪一下sql的中的left join 或者right join 或者inner join 中的 on和where的区别，想了解这个首先我们要了解两个基础的知识。

1.join的三种连接方式的区别：

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录  

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 

inner join(等值连接) 只返回两个表中联结字段相等的行

笛卡尔积：两个[集合]*X*和*Y*的笛卡尓积（Cartesian product），又称[直积]，表示为*X* × *Y*，第一个对象是*X*的成员而第二个对象是*Y*的所有可能[有序对]的其中一个成员

有了上面的两个知识之后，我们来看一下实例

先准备两张需要使用的表

mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 李四   |
|  3 | 陈五   |
|  4 | 阿宝   |
|  5 | 李哥   |
|  6 | marry  |
|  7 | 股东   |
|  8 | 百世   |
|  9 | 退伍   |
+----+--------+

mysql> select * from user_info;
+----+------+--------+
| id | age  | height |
+----+------+--------+
|  1 |    1 | 888    |
|  3 |    4 | 555    |
|  5 |    7 | 132    |
|  6 |    8 | 111    |
|  7 |   11 | 222    |
|  8 |   13 | 12     |
+----+------+--------+

下面我们来执行sql语句看看 inner join

select a.*,b.* from user a inner join user_info b on a.id = b.id;
select a.*,b.* from user a inner join user_info b on a.id = b.id and a.id != 1;
select a.*,b.* from user a inner join user_info b on a.id = b.id where a.id != 1;
select a.*,b.* from user a inner join user_info b on a.id = b.id and b.id != 1;
select a.*,b.* from user a inner join user_info b on a.id = b.id where b.id != 1;

mysql> select a.*,b.* from user a inner join user_info b on a.id = b.id;
+----+--------+----+------+--------+
| id | name   | id | age  | height |
+----+--------+----+------+--------+
|  1 | 张三   |  1 |    1 | 888    |
|  3 | 陈五   |  3 |    4 | 555    |
|  5 | 李哥   |  5 |    7 | 132    |
|  6 | marry  |  6 |    8 | 111    |
|  7 | 股东   |  7 |   11 | 222    |
|  8 | 百世   |  8 |   13 | 12     |
+----+--------+----+------+--------+
6 rows in set (0.00 sec)

mysql> select a.*,b.* from user a inner join user_info b on a.id = b.id and a.id != 1;
+----+--------+----+------+--------+
| id | name   | id | age  | height |
+----+--------+----+------+--------+
|  3 | 陈五   |  3 |    4 | 555    |
|  5 | 李哥   |  5 |    7 | 132    |
|  6 | marry  |  6 |    8 | 111    |
|  7 | 股东   |  7 |   11 | 222    |
|  8 | 百世   |  8 |   13 | 12     |
+----+--------+----+------+--------+
5 rows in set (0.00 sec)

mysql> select a.*,b.* from user a inner join user_info b on a.id = b.id where a.id != 1;
+----+--------+----+------+--------+
| id | name   | id | age  | height |
+----+--------+----+------+--------+
|  3 | 陈五   |  3 |    4 | 555    |
|  5 | 李哥   |  5 |    7 | 132    |
|  6 | marry  |  6 |    8 | 111    |
|  7 | 股东   |  7 |   11 | 222    |
|  8 | 百世   |  8 |   13 | 12     |
+----+--------+----+------+--------+
5 rows in set (0.00 sec)

mysql> select a.*,b.* from user a inner join user_info b on a.id = b.id and b.id != 1;
+----+--------+----+------+--------+
| id | name   | id | age  | height |
+----+--------+----+------+--------+
|  3 | 陈五   |  3 |    4 | 555    |
|  5 | 李哥   |  5 |    7 | 132    |
|  6 | marry  |  6 |    8 | 111    |
|  7 | 股东   |  7 |   11 | 222    |
|  8 | 百世   |  8 |   13 | 12     |
+----+--------+----+------+--------+
5 rows in set (0.00 sec)

mysql> select a.*,b.* from user a inner join user_info b on a.id = b.id where b.id != 1;
+----+--------+----+------+--------+
| id | name   | id | age  | height |
+----+--------+----+------+--------+
|  3 | 陈五   |  3 |    4 | 555    |
|  5 | 李哥   |  5 |    7 | 132    |
|  6 | marry  |  6 |    8 | 111    |
|  7 | 股东   |  7 |   11 | 222    |
|  8 | 百世   |  8 |   13 | 12     |
+----+--------+----+------+--------+
5 rows in set (0.00 sec)

结论：在inner join 中on 和 where 是没有区别的

下面我们来执行sql语句看看 left join

select a.*,b.* from user a left join user_info b on a.id = b.id;
select a.*,b.* from user a left join user_info b on a.id = b.id and a.id != 1;
select a.*,b.* from user a left join user_info b on a.id = b.id where a.id != 1;
select a.*,b.* from user a left join user_info b on a.id = b.id and b.id != 1;
select a.*,b.* from user a left join user_info b on a.id = b.id where b.id != 1;

mysql> select a.*,b.* from user a left join user_info b on a.id = b.id;
+----+--------+------+------+--------+
| id | name   | id   | age  | height |
+----+--------+------+------+--------+
|  1 | 张三   |    1 |    1 | 888    |
|  2 | 李四   | NULL | NULL | NULL   |
|  3 | 陈五   |    3 |    4 | 555    |
|  4 | 阿宝   | NULL | NULL | NULL   |
|  5 | 李哥   |    5 |    7 | 132    |
|  6 | marry  |    6 |    8 | 111    |
|  7 | 股东   |    7 |   11 | 222    |
|  8 | 百世   |    8 |   13 | 12     |
|  9 | 退伍   | NULL | NULL | NULL   |
+----+--------+------+------+--------+
9 rows in set (0.00 sec)

mysql> select a.*,b.* from user a left join user_info b on a.id = b.id and a.id != 1;
+----+--------+------+------+--------+
| id | name   | id   | age  | height |
+----+--------+------+------+--------+
|  1 | 张三   | NULL | NULL | NULL   |
|  2 | 李四   | NULL | NULL | NULL   |
|  3 | 陈五   |    3 |    4 | 555    |
|  4 | 阿宝   | NULL | NULL | NULL   |
|  5 | 李哥   |    5 |    7 | 132    |
|  6 | marry  |    6 |    8 | 111    |
|  7 | 股东   |    7 |   11 | 222    |
|  8 | 百世   |    8 |   13 | 12     |
|  9 | 退伍   | NULL | NULL | NULL   |
+----+--------+------+------+--------+
9 rows in set (0.00 sec)

mysql> select a.*,b.* from user a left join user_info b on a.id = b.id where a.id != 1;
+----+--------+------+------+--------+
| id | name   | id   | age  | height |
+----+--------+------+------+--------+
|  2 | 李四   | NULL | NULL | NULL   |
|  3 | 陈五   |    3 |    4 | 555    |
|  4 | 阿宝   | NULL | NULL | NULL   |
|  5 | 李哥   |    5 |    7 | 132    |
|  6 | marry  |    6 |    8 | 111    |
|  7 | 股东   |    7 |   11 | 222    |
|  8 | 百世   |    8 |   13 | 12     |
|  9 | 退伍   | NULL | NULL | NULL   |
+----+--------+------+------+--------+
8 rows in set (0.00 sec)

mysql> select a.*,b.* from user a left join user_info b on a.id = b.id and b.id != 1;
+----+--------+------+------+--------+
| id | name   | id   | age  | height |
+----+--------+------+------+--------+
|  1 | 张三   | NULL | NULL | NULL   |
|  2 | 李四   | NULL | NULL | NULL   |
|  3 | 陈五   |    3 |    4 | 555    |
|  4 | 阿宝   | NULL | NULL | NULL   |
|  5 | 李哥   |    5 |    7 | 132    |
|  6 | marry  |    6 |    8 | 111    |
|  7 | 股东   |    7 |   11 | 222    |
|  8 | 百世   |    8 |   13 | 12     |
|  9 | 退伍   | NULL | NULL | NULL   |
+----+--------+------+------+--------+
9 rows in set (0.00 sec)

mysql> select a.*,b.* from user a left join user_info b on a.id = b.id where b.id != 1;
+----+--------+------+------+--------+
| id | name   | id   | age  | height |
+----+--------+------+------+--------+
|  3 | 陈五   |    3 |    4 | 555    |
|  5 | 李哥   |    5 |    7 | 132    |
|  6 | marry  |    6 |    8 | 111    |
|  7 | 股东   |    7 |   11 | 222    |
|  8 | 百世   |    8 |   13 | 12     |
+----+--------+------+------+--------+
5 rows in set (0.00 sec)

结论：left join时进行笛卡尔积之后on后面的条件只对右表有效，并且如果右表用了where还是两个表都会取交集，进行过滤。

类似：如果是right join的话

right join时进行笛卡尔积之后on后面的条件只对左表有效，并且如果左表用了where还是两个表都会取交集，进行过滤。

有对结论有疑问者，欢迎讨论~~~

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2018-09-02，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自利志分享微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度

sql中的过滤条件放在on和where的区别

sql中的过滤条件放在on和where的区别

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐