mysql left join用法详解

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. 语句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. 语句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. 语句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 可对右表进行限制。