1. left join
LEFT JOIN
:寻找left join
左边的表的记录。
系统会将A表
与B表
进行笛卡尔积运算,去掉A表
中为NULL
的记录。
进行left join
时,有时需要设置条件。主表的条件写在WHERE
后面,辅表的条件写在ON
后面。
2. 实例
表结构:
table people(id, age):
id age
--------------------------
1 21
2 21
3 23
table job(id, type):
id type
---------------------------
1 1
2 2
3. SQL语句举例
- 语句1:
SELECT people.*, job.* FROM people LEFT JOIN job ON people.id = job.id AND people.age = 21;
people.id people.age job.id job.type ------------------------------------------------------------- 1 21 1 1 2 21 2 2 3 23
- 语句2:
SELECT people.*, job.* FROM people LEFT JOIN job ON people.id = job.id WHERE people.age = 21;
people.id people.age job.id job.type ------------------------------------------------------------- 1 21 1 1 2 21 2 2
- 语句3:
SELECT people.*, job.* FROM people LEFT JOIN job ON people.id = job.id AND job.type = 1;
people.id people.age job.id job.type ------------------------------------------------------------- 1 21 1 1 2 21 3 23
4. 总结
语句1
:left join
中左表的全部记录将被全部查询,on
后面的条件不起作用。语句2
:加上where
后可对左表进行筛选。语句3
:on
可对右表进行限制。