LEFT JOIN关联查询中ON条件和WHERE条件浅析

首先，需要注意的是，使用LEFT JOIN进行连接查询时，不论ON条件是否为真，都会返回左表的所有记录

 

1.  ON条件是生成临时表时使用的条件（仅在关联右表查询时对右表筛选起作用，无论条件是否为真都会返回左表所有记录）
2. WHERE条件是生成临时表后使用的条件（对生成的临时表再次进行条件过滤，此时条件筛选对全表有效，条件不为真就会过滤掉）

假设我们有class表和achieve表，分别是班级表和成绩表

class表（班级表）

班级（class）	成员（student）
一班	张三
二班	李四
三班	王五

achieve表（成绩表）

成员（student）	课程（courses）	成绩（result）
张三	语文	70
李四	数学	85
李四	英语	90

 

• 仅on条件关联查询

select * from class a left join achieve b on a.student = b.student;

a.class	a.student	b.student	b.courses	b.result
一班	张三	张三	语文	70
二班	李四	李四	数学	85
二班	李四	李四	英语	90
三班	王五

• on条件关联查询 + on条件筛选（右表筛选条件） 

select * from class a left join achieve b on a.student = b.student and b.result = 70;

a.class	a.student	b.student	b.courses	b.result
一班	张三	张三	语文	70
二班	李四
三班	王五

注：根据left join on条件的关联查询规则，会取左表所有记录，再与右表关联记录时，会限制右表b.result = 70，故右表仅有一条记录符合条件；

• on条件关联查询 + on条件筛选（左表筛选条件）

select * from class a left join achieve b on a.student = b.student and a.student = '李四';

 

a.class	a.student	b.student	b.courses	b.result
一班	张三
二班	李四	李四	数学	85
二班	李四	李四	英语	90
三班	王五

注：根据left join on条件的关联查询规则，会取左表所有记录，再与右表关联记录时，会限制左表a.student = '李四'去关联右表，故右表仅有b.student = ‘李四’ 的两条记录符合条件；

• on条件关联查询 + where条件筛选

select * from class a left join achieve b on a.student = b.student where b.result = 70;

a.class	a.student	b.student	b.courses	b.result
一班	张三	张三	语文	70

注：根据left join on条件的关联查询规则，会取左表所有记录，再与右表关联记录；然后where条件会对关联后的全表进行筛选，此时再筛选b.student = 70 ，仅有一条全表记录符合条件；