数据库中on条件与where条件的区别
标签:数据库
sql">mysql> SELECT e.empno,ename,e.deptno,e.is_deleted FROM `emp_test` e ;
+-------+-------+--------+------------+
| empno | ename | deptno | is_deleted |
+-------+-------+--------+------------+
| 1 | 张三 | 1 | 0 |
| 2 | 李四 | 1 | 1 |
| 3 | 王五 | 10 | 0 |
+-------+-------+--------+------------+
3 rows in set (0.07 sec)
mysql> select * from dept;
+--------+--------+------+
| deptno | dname | loc |
+--------+--------+------+
| 1 | 开发部 | 北京 |
| 2 | 测试部 | 上海 |
| 3 | 市场部 | 广州 |
| 4 | 运营部 | 杭州 |
+--------+--------+------+
4 rows in set (0.06 sec)
where条件
sql">-- 因为e.is_deleted = 0再过滤条件中,所以不会出现再结果集中
mysql> SELECT e.empno,ename,e.deptno as edeptno,e.is_deleted,dname,d.deptno as ddeptno FROM `emp_test` e left join dept d on e.deptno = d.deptno where e.is_deleted = 0;
+-------+-------+---------+------------+--------+---------+
| empno | ename | edeptno | is_deleted | dname | ddeptno |
+-------+-------+---------+------------+--------+---------+
| 1 | 张三 | 1 | 0 | 开发部 | 1 |
| 3 | 王五 | 10 | 0 | NULL | NULL |
+-------+-------+---------+------------+--------+---------+
2 rows in set (0.07 sec)
实现细节:
-
生成笛卡尔积
sql">+-------+-------+---------+------------+---------+--------+ | empno | ename | edeptno | is_deleted | ddeptno | dname | +-------+-------+---------+------------+---------+--------+ | 1 | 张三 | 1 | 0 | 1 | 开发部 | | 1 | 张三 | 1 | 0 | 2 | 测试部 | | 1 | 张三 | 1 | 0 | 3 | 市场部 | | 1 | 张三 | 1 | 0 | 4 | 运营部 | | 2 | 李四 | 1 | 1 | 1 | 开发部 | | 2 | 李四 | 1 | 1 | 2 | 测试部 | | 2 | 李四 | 1 | 1 | 3 | 市场部 | | 2 | 李四 | 1 | 1 | 4 | 运营部 | | 3 | 王五 | 10 | 0 | 1 | 开发部 | | 3 | 王五 | 10 | 0 | 2 | 测试部 | | 3 | 王五 | 10 | 0 | 3 | 市场部 | | 3 | 王五 | 10 | 0 | 4 | 运营部 | +-------+-------+---------+------------+---------+--------+ -
执行on子句(e.deptno = d.deptno)
sql">+-------+-------+---------+------------+---------+--------+ | empno | ename | edeptno | is_deleted | ddeptno | dname | +-------+-------+---------+------------+---------+--------+ | 1 | 张三 | 1 | 0 | 1 | 开发部 | | 2 | 李四 | 1 | 1 | 1 | 开发部 | +-------+-------+---------+------------+---------+--------+ -
执行join子句
left join 会把左表中有on过滤后的临时表中没有的添加进来,右表用null填充
right会把右表中有on过滤后的临时表中没有的添加进来,左表用null填充
故将王五添加进来,并且右表填充null
sql">+-------+-------+---------+------------+---------+--------+ | empno | ename | edeptno | is_deleted | ddeptno | dname | +-------+-------+---------+------------+---------+--------+ | 1 | 张三 | 1 | 0 | 1 | 开发部 | | 2 | 李四 | 1 | 1 | 1 | 开发部 | | 3 | 王五 | 10 | 0 | NULL | NULL | +-------+-------+---------+------------+---------+--------+ -
执行where子句
sql">+-------+-------+---------+------------+---------+--------+ | empno | ename | edeptno | is_deleted | ddeptno | dname | +-------+-------+---------+------------+---------+--------+ | 1 | 张三 | 1 | 0 | 1 | 开发部 | | 3 | 王五 | 10 | 0 | NULL | NULL | +-------+-------+---------+------------+---------+--------+
on条件
-- empno=2因为不满足e.is_deleted = 0故不会连表,dept对应部分为null(只有完全满足连表条件才会连表)
mysql> SELECT e.empno,ename,e.deptno as edeptno,e.is_deleted,dname,d.deptno as ddeptno FROM `emp_test`e left join dept d on e.deptno = d.deptno and e.is_deleted = 0 ;
+-------+-------+---------+------------+--------+---------+
| empno | ename | edeptno | is_deleted | dname | ddeptno |
+-------+-------+---------+------------+--------+---------+
| 1 | 张三 | 1 | 0 | 开发部 | 1 |
| 2 | 李四 | 1 | 1 | NULL | NULL |
| 3 | 王五 | 10 | 0 | NULL | NULL |
+-------+-------+---------+------------+--------+---------+
3 rows in set (0.10 sec)
实现细节:
-
生成笛卡尔积
sql">+-------+-------+---------+------------+---------+--------+ | empno | ename | edeptno | is_deleted | ddeptno | dname | +-------+-------+---------+------------+---------+--------+ | 1 | 张三 | 1 | 0 | 1 | 开发部 | | 1 | 张三 | 1 | 0 | 2 | 测试部 | | 1 | 张三 | 1 | 0 | 3 | 市场部 | | 1 | 张三 | 1 | 0 | 4 | 运营部 | | 2 | 李四 | 1 | 1 | 1 | 开发部 | | 2 | 李四 | 1 | 1 | 2 | 测试部 | | 2 | 李四 | 1 | 1 | 3 | 市场部 | | 2 | 李四 | 1 | 1 | 4 | 运营部 | | 3 | 王五 | 10 | 0 | 1 | 开发部 | | 3 | 王五 | 10 | 0 | 2 | 测试部 | | 3 | 王五 | 10 | 0 | 3 | 市场部 | | 3 | 王五 | 10 | 0 | 4 | 运营部 | +-------+-------+---------+------------+---------+--------+ -
执行on子句(e.deptno = d.deptno and e.is_deleted = 0)
sql">+-------+-------+---------+------------+---------+--------+ | empno | ename | edeptno | is_deleted | ddeptno | dname | +-------+-------+---------+------------+---------+--------+ | 1 | 张三 | 1 | 0 | 1 | 开发部 | +-------+-------+---------+------------+---------+--------+ -
执行join子句
将被on条件过滤掉的李四和王五加回来,右表填充null
+-------+-------+---------+------------+--------+---------+ | empno | ename | edeptno | is_deleted | dname | ddeptno | +-------+-------+---------+------------+--------+---------+ | 1 | 张三 | 1 | 0 | 开发部 | 1 | | 2 | 李四 | 1 | 1 | NULL | NULL | | 3 | 王五 | 10 | 0 | NULL | NULL | +-------+-------+---------+------------+--------+---------+
sqlfromonjoinwhere_167">sql执行顺序之from>on>join>where
-
生成笛卡尔积
-
执行on子句过滤
-
执行join子句回填数据
left join 回填被on过滤掉的左表数据,右表用null填充
right join 回填被on过滤掉的右表的数据,左表用null填充
inner join 不处理
sql_181">完整的sql执行顺序
from>on>join>where>group by>having>select>distinct>order by>limit
