SQL中join操作后面的on 和 where 的区别

join关键字的作用是将多个表按一定的条件联合起来,从而可以实现从多个表中获取数据。
join的常见用法有join、left join 、right join 、full join。

on 和 where 的区别:

on 表示在 join 前进行条件筛选,然后再进行join操作。
where 表示在join操作完了再做过滤。

示例:
现在有两张表t1 和 t2,表里面的数据如下:

> select * from t1;
+------+------+
| id   | age  |
+------+------+
|    1 |   20 |
|    2 |   21 |
|    3 |   22 |
|    4 |   23 |
|    5 |   24 |
+------+------+

> select * from t2;
+------+------+
| id   | name |
+------+------+
|    1 | Lee  |
|    2 | Bob  |
|    3 | Kate |
|    4 | Tony |
+------+------+

思考一下下面的sql 执行后的结果,有几条数据:

1)select * from t1 left join t2 on t1.id=t2.id and t1.id=1;
2)select * from t1 left join t2 on t1.id=t2.id and t2.id=1;
3)select * from t1 left join t2 on t1.id=t2.id where t1.id=1;
4)select * from t1 left join t2 on t1.id=t2.id where t2.id=1;

思考5秒钟......

1 seconds...
2 seconds...
3 seconds...
4 seconds...
5 seconds...

答案是:5、5、1、1

实际输出结果如下:

1) select * from t1 left join t2 on t1.id=t2.id and t1.id=1;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   20 |    1 | Lee  |
|    2 |   21 | NULL | NULL |
|    3 |   22 | NULL | NULL |
|    4 |   23 | NULL | NULL |
|    5 |   24 | NULL | NULL |
+------+------+------+------+

2) select * from t1 left join t2 on t1.id=t2.id and t2.id=1;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   20 |    1 | Lee  |
|    2 |   21 | NULL | NULL |
|    3 |   22 | NULL | NULL |
|    4 |   23 | NULL | NULL |
|    5 |   24 | NULL | NULL |
+------+------+------+------+

3) select  * from t1 left join t2 on t1.id=t2.id where t1.id=1;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   20 |    1 | Lee  |
+------+------+------+------+

4) select  * from t1 left join t2 on t1.id=t2.id where t2.id=1;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   20 |    1 | Lee  |
+------+------+------+------+

注意一点:
left join 时,左表的数据是完整的,on 条件会筛选需要关联的列,无论能否关联上,左表都是完整的。比如下面的例子:

> select * from t1 left join t2 on t1.id=t2.id and t2.id=10;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   20 | NULL | NULL |
|    2 |   21 | NULL | NULL |
|    3 |   22 | NULL | NULL |
|    4 |   23 | NULL | NULL |
|    5 |   24 | NULL | NULL |
+------+------+------+------+

同理,right join 也是一样的。

SQL中join操作后面的on 和 where 的区别

上一篇:Docker (3) 使用docker 安装mysql、tomcat、ngnix、redis


下一篇:sql中模糊查询和在开始和结束时间之间