学习笔记 - 数据库 - join 条件和 where 条件查询

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 语句而言，

1. 首先生成条件为 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 |
   +----+------+---------+----+------+-----------+---------+

2. 然后在对生成的链接表用 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 语句而言，同样是先成链接表，然后在条件过滤，

1. 生成条件为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 |
   +------+------+---------+----+------+-----------+---------+

2. 然后进行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 |
+------+------+---------+----+------+-----------+---------+

对于主表（右表）依旧是返回所有记录，但是对于辅表（左表）则仅仅返回了符合两个条件的部分。