03-008 连接查询left join中on和where

对于左连接中left join中的on和where区别
w3school SQL LEFT JOIN 关键字介绍

员工表和员工地址表

MariaDB [my_db]> select * from employee;
+------+----------+-----------+----------+
| e_id | LastName | FirstName | hometown |
+------+----------+-----------+----------+
| 1    |  Jack    | Tom       | Shanghai |
| 2    | Frank    | Linu      | Shanghai |
| 3    | Mark     | Job       | London   |
| 4    | Charles  | Min       | Beijing  |
| 5    | Brown    | Richard   | New York |
+------+----------+-----------+----------+
5 rows in set (0.00 sec)

#员工地址表
MariaDB [my_db]> select * from address;
+----+---------+----------+
| id | Country | Hometown |
+----+---------+----------+
|  1 | CH      | Shanghai |
|  2 | UK      | London   |
|  3 | USA     | New York |
+----+---------+----------+
3 rows in set (0.00 sec)

一、两个表的left join on如下

MariaDB [my_db]> select * from employee e1 left join address a1 on e1.hometown=a1.hometown; 
+------+----------+-----------+----------+------+---------+----------+
| e_id | LastName | FirstName | hometown | id   | Country | Hometown |
+------+----------+-----------+----------+------+---------+----------+
| 1    |  Jack    | Tom       | Shanghai |    1 | CH      | Shanghai |
| 2    | Frank    | Linu      | Shanghai |    1 | CH      | Shanghai |
| 3    | Mark     | Job       | London   |    2 | UK      | London   |
| 5    | Brown    | Richard   | New York |    3 | USA     | New York |
| 4    | Charles  | Min       | Beijing  | NULL | NULL    | NULL     |
+------+----------+-----------+----------+------+---------+----------+
5 rows in set (0.00 sec)

1.1、on后面跟and及on where后面跟and

MariaDB [my_db]> select * from employee e1 left join address a1 on e1.hometown=a1.hometown and a1.country = 'CH'; 
+------+----------+-----------+----------+------+---------+----------+
| e_id | LastName | FirstName | hometown | id   | Country | Hometown |
+------+----------+-----------+----------+------+---------+----------+
| 1    |  Jack    | Tom       | Shanghai |    1 | CH      | Shanghai |
| 2    | Frank    | Linu      | Shanghai |    1 | CH      | Shanghai |
| 3    | Mark     | Job       | London   | NULL | NULL    | NULL     |
| 4    | Charles  | Min       | Beijing  | NULL | NULL    | NULL     |
| 5    | Brown    | Richard   | New York | NULL | NULL    | NULL     |
+------+----------+-----------+----------+------+---------+----------+
5 rows in set (0.00 sec)

#LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行

MariaDB [my_db]> select * from employee e1 left join address a1 on e1.hometown=a1.hometown where a1.hometown =  'Shanghai'; 
+------+----------+-----------+----------+----+---------+----------+
| e_id | LastName | FirstName | hometown | id | Country | Hometown |
+------+----------+-----------+----------+----+---------+----------+
| 1    |  Jack    | Tom       | Shanghai |  1 | CH      | Shanghai |
| 2    | Frank    | Linu      | Shanghai |  1 | CH      | Shanghai |
+------+----------+-----------+----------+----+---------+----------+
2 rows in set (0.00 sec)

#where条件是在left join之外，是对连接之后的结果再次过滤

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回。

在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。

---

二、inner join 是left join 和right join的交集

MariaDB [my_db]> select * from employee e1 inner join address a1 on e1.hometown=a1.hometown; 
+------+----------+-----------+----------+----+---------+----------+
| e_id | LastName | FirstName | hometown | id | Country | Hometown |
+------+----------+-----------+----------+----+---------+----------+
| 1    |  Jack    | Tom       | Shanghai |  1 | CH      | Shanghai |
| 2    | Frank    | Linu      | Shanghai |  1 | CH      | Shanghai |
| 3    | Mark     | Job       | London   |  2 | UK      | London   |
| 5    | Brown    | Richard   | New York |  3 | USA     | New York |
+------+----------+-----------+----------+----+---------+----------+
4 rows in set (0.00 sec)


MariaDB [my_db]> select * from employee e1 inner join address a1 on e1.hometown=a1.hometown and a1.country =  'CH'; 
+------+----------+-----------+----------+----+---------+----------+
| e_id | LastName | FirstName | hometown | id | Country | Hometown |
+------+----------+-----------+----------+----+---------+----------+
| 1    |  Jack    | Tom       | Shanghai |  1 | CH      | Shanghai |
| 2    | Frank    | Linu      | Shanghai |  1 | CH      | Shanghai |
+------+----------+-----------+----------+----+---------+----------+
2 rows in set (0.00 sec)


MariaDB [my_db]> select * from employee e1 inner join address a1 on e1.hometown=a1.hometown where a1.country =  'CH'; 
+------+----------+-----------+----------+----+---------+----------+
| e_id | LastName | FirstName | hometown | id | Country | Hometown |
+------+----------+-----------+----------+----+---------+----------+
| 1    |  Jack    | Tom       | Shanghai |  1 | CH      | Shanghai |
| 2    | Frank    | Linu      | Shanghai |  1 | CH      | Shanghai |
+------+----------+-----------+----------+----+---------+----------+
2 rows in set (0.00 sec)