join on 查询条件和 where 查询条件
on 是生成符合条件的表,where 是对已生成的表进行条件过滤
例如,有如下两表:
user:
+----+-------+---------+
| id | name | addr |
+----+-------+---------+
| 1 | tom | street1 |
| 2 | DIVA | street2 |
| 3 | simis | street2 |
+----+-------+---------+
record:
+----+------+-----------+---------+
| id | msg | date | user_id |
+----+------+-----------+---------+
| 1 | bai | 1233 | 1 |
| 2 | wds | 132233 | 2 |
| 3 | west | asf132233 | 1 |
| 4 | hfd | 34fw | NULL |
+----+------+-----------+---------+
使用内链接查询时,以下两条 sql 语句查询结果是相同的,
mysql> select * from user join record on user.id=record.user_id where record.id=3;
mysql> select * from user join record on user.id=record.user_id and record.id=3;
查询结果为:
+----+------+---------+----+------+-----------+---------+
| id | name | addr | id | msg | date | user_id |
+----+------+---------+----+------+-----------+---------+
| 1 | tom | street1 | 3 | west | asf132233 | 1 |
+----+------+---------+----+------+-----------+---------+
但是它们的执行过程存在很大区别:
对第一条 sql 语句而言,
首先生成条件为
user.id=record.user_id
的链接表+----+------+---------+----+------+-----------+---------+ | id | name | addr | id | msg | date | user_id | +----+------+---------+----+------+-----------+---------+ | 1 | tom | street1 | 1 | bai | 1233 | 1 | | 2 | DIVA | street2 | 2 | wds | 132233 | 2 | | 1 | tom | street1 | 3 | west | asf132233 | 1 | +----+------+---------+----+------+-----------+---------+
- 然后在对生成的链接表用
where
条件过滤查找recod.id=3
而对第二条 sql 语句则是在生成链接表时已经完成了条件判断。
使用左右链接(left join)时,可以体现这个过程
mysql> select * from user right join record on user.id=record.user_id where record.id=3;
mysql> select * from user right join record on user.id=record.user_id and record.id=3;
对于第一条 sql 语句而言,同样是先成链接表,然后在条件过滤,
生成条件为
user.id=record.user_id
的右链接表+------+------+---------+----+------+-----------+---------+ | id | name | addr | id | msg | date | user_id | +------+------+---------+----+------+-----------+---------+ | 1 | tom | street1 | 1 | bai | 1233 | 1 | | 1 | tom | street1 | 3 | west | asf132233 | 1 | | 2 | DIVA | street2 | 2 | wds | 132233 | 2 | | NULL | NULL | NULL | 4 | hfd | 34fw | NULL | +------+------+---------+----+------+-----------+---------+
然后进行
record.id=3
条件过滤+----+------+---------+----+------+-----------+---------+ | id | name | addr | id | msg | date | user_id | +----+------+---------+----+------+-----------+---------+ | 1 | tom | street1 | 3 | west | asf132233 | 1 | +----+------+---------+----+------+-----------+---------+
而对于第二条 sql 语句来说并不是这样,它将直接返回符合条件的记录:
+------+------+---------+----+------+-----------+---------+
| id | name | addr | id | msg | date | user_id |
+------+------+---------+----+------+-----------+---------+
| 1 | tom | street1 | 3 | west | asf132233 | 1 |
| NULL | NULL | NULL | 1 | bai | 1233 | 1 |
| NULL | NULL | NULL | 2 | wds | 132233 | 2 |
| NULL | NULL | NULL | 4 | hfd | 34fw | NULL |
+------+------+---------+----+------+-----------+---------+
对于主表(右表)依旧是返回所有记录,但是对于辅表(左表)则仅仅返回了符合两个条件的部分。