left-join-执行原理

create table class
(

class_id varchar(10),
class_name varchar(50)

)

insert into class values (1,’ 语文 ’);
insert into class values (2,’ 数学 ’);
insert into class values (3,’ 英语 ’);

create table score
(

class_id varchar(10),
Student_id varchar(10),
Score int

)

insert into score values (1,’A001′,90);
insert into score values (2,’A001′,95);
insert into score values (1,’A002′,80);
insert into score values (2,’A002′,85);
insert into score values (1,’B001′,88);

select * from class
select * from score

– 一个课程表，一个成绩表
– 表数据分布是
– 成绩表：有三个学生 A,B,C，
–A,B 都有语文和数学成绩，
–C 只有语文成绩；
–A，B，C 都没有英语成绩，
– 课程表，有语文，数学，英语三门课程，没有“体育”这门课程

– 如果你对 left join 足够熟悉的话，先不要看结果，是否可以直接说出下面查询的结果

select * from class A left join score B on A.class_id=B.class_id and A.class_name=’ 语文 ’
select * from class A left join score B on A.class_id=B.class_id and A.class_name=’ 数学 ’
select * from class A left join score B on A.class_id=B.class_id and A.class_name=’ 英语 ’
select * from class A left join score B on A.class_id=B.class_id and A.class_name=’ 体育 ’

select * from class A left join score B on A.class_id=B.class_id
where A.class_name=’ 语文 ’

select * from class A left join score B on A.class_id=B.class_id
where A.class_name=’ 数学 ’

select * from class A left join score B on A.class_id=B.class_id
where A.class_name=’ 英语 ’

select * from class A left join score B on A.class_id=B.class_id
where A.class_name=’ 体育 ’

– 其实结果我不想贴了，以后再整理，如果结果跟你预期的不一样，
– 或者你对查询结果感到疑惑，下面就是对上面结果的解释

/过滤条件在 on 中时*/

– 不管 on 中的条件如何写，左连接首先要得到左边的所有的行
– 对于右边的表，如果满足连接条件，则输出值，如果不满足条件，则返回 null
– 也就是说，on 中的过滤条件（比如 on leftTable.id=rightTable.id  and leftTable.colName=’*‘），
– 是作为左外连接的连接条件，而不是过滤条件
– 左边表的数据是要全部输出的，做联接的时候，
– 抽取符合（leftTable.colName=’*‘）条件的的左边的数据行，跟右边的表做连接，输出结果
– 不符合（leftTable.colName=’*‘）条件的数据行，输出左边表，右边表为 null 值，输出结果
比如这个 sql：
select * from class A left join score B on A.class_id=B.class_id and A.class_name=’ 英语 ’

查询过程是这样的：
找出 A 表的第一条数据，判断是否符合 class_name=’ 英语 ’
如果符合，则跟右表做，输出 A 表 + B 表的行（当然存在 B 表中可能没有相关 ID 的英语成绩，那么 B 表也可以输出 null）
如果不符，直接输出 A 表中的行，B 表中的行直接为 null

从这个过程可以看出，on 中的等值条件只是影响 left join 产生结果集的过程，并不参与数据的过滤
A 表总是将其所有的值输出的（不管过滤条件是啥，不管 B 表是否存在相关信息）

/过滤条件在 where 中时**/

– 但是过滤条件写在 where 中（on leftTable.id=rightTable.id where  leftTable.colName=’*‘）时，
– 是作为对左边表的“过滤条件”，就意味着先根据 where（where  leftTable.colName=’*‘）条件过滤左边的表
– 然后将过滤后的结果跟右边的表做左联接连接