on和where
所有的查询都回产生一个中间临时报表,查询结果就是从返回临时报表中得到。on和where后面所跟限制条件的区别,主要与限制条件起作用的时机有关,on根据限制条件对数据库记录进行过滤,然后生产临时报表;而where是在临时报表生产之后,根据限制条件从临时报表中筛选结果。
总结:在左外连接中,on会返回左表中的所有记录;而where中,此时相当于inner join,只会返回满足条件的记录。
速度:因为on限制条件发生时间较早,产生的临时报表数据集要小,因此on的性能要优于where。
having和where
having和where的区别也是与限制条件起作用时机有关,having是在聚集函数计算结果出来之后筛选结果,查询结果只返回符合条件的分组,having不能单独出现,只能出现在group by子句中。而where是在计算之前筛选结果,如果聚集函数使用where,那么聚集函数只计算满足where子句限制条件的数据。
总结:where即可以和select等其他子句搭配使用,也可以和group by子句搭配使用,where的优先级要高于having。
速度:因为where在聚集函数之前筛选数据,having在计算之后筛选分组,因此where的性能要优于having
举例:在左连接时使用不同多个限制条件需要注意的地方
mysql> select * from card;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 饭卡 |
| 2 | 建行卡 |
| 3 | 农行卡 |
| 4 | 工商卡 |
| 5 | 邮政卡 |
+------+-----------+
mysql> select * from person;
+------+--------+--------+
| id | name | cardId|
+------+--------+--------+
| 1 | 张三 | 1 |
| 2 | 李四 | 3 |
| 3 | 王五 | 6 |
+------+--------+--------+
mysql> select * from person p left join card c on cardId=c.id;
+------+--------+---------+--------+-----------+
| id | name | cardId | id | name |
+------+--------+---------+--------+-----------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | 3 | 农行卡 |
| 3 | 王五 | 6 | NULL | NULL |
+------+--------+---------+--------+-----------+
left join查询会从左表那里返回所有的行,即使在右表中没有匹配的行。查询中on条件只有一个,因此不存在特殊注意之处。但是当我们on条件如果存在多个时候会出现一些与我们预期不符的查询结果。
例如此时我希望对上述的查询结果再限制一个条件,我只想要p.id=1的法外狂徒的信息。
mysql> select * from person p left join card c on cardId=c.id and p.id = 1;
+------+--------+---------+--------+--------+
| id | name | cardId | id | name |
+------+--------+---------+--------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
| 2 | 李四 | 3 | NULL | NULL |
| 3 | 王五 | 6 | NULL | NULL |
+------+--------+---------+--------+--------+
为什么已经限制了p.id=1 却查询结果还有其他人呢?我们换用where约束试试。
mysql> select * from person p left join card c on cardId=c.id where p.id = 1;
+------+--------+--------+------+--------+
| id | name | cardId| id | name |
+------+--------+--------+------+--------+
| 1 | 张三 | 1 | 1 | 饭卡 |
+------+--------+--------+------+--------+
这次确实只有p.id=1的张三了。那为什么第一个查询语句会与预期不符?回顾一下left join的定义,左边表会返回所有行,所以left join如果对左边表进行约束的话是不会生效的。但是,对left join的右边表添加条件的话是生效的!