SQL面试题 Join连接细节

 

有两个表a和b，两字段完全相同：（id int ,name varchar(10)....）都有以下数据：

id     name

---   ----------

1         a

2         b

1、创建表a,b

 create table a(
        id int,
        name varchar(20)
    )engine=innodb DEFAULT charset=utf8;

create table b(
        id int,
        name varchar(20)
    )engine=innodb DEFAULT charset=utf8;

2、插入数据

insert into a (id,name)  values(1,a);
insert into a (id,name)  values(2,b);
        
insert into b (id,name)  values(1,a);
insert into b (id,name)  values(2,b);

3、练习题 有以下查询语言，写出他的运行结果

题目一：

1、

select * from  a left join b

on a.id=b.id

where a.id = 1 

解析：
左连接：根据ON后给出的两表的条件将两表连接起来，
结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分，
不满足用null显示。

最后通过where条件筛选，显示满足where条件的数据（a.id=1，所以不会返回left中左表所有数据了）

题目二：

2、

select * from  a left join b

on a.id=b.id and a.id = 1 

解析：
注意！！！！（看一下上边左连接的图片，重要的事情多标注，多看第一题左连接的含义）

left join 取左边表的所有数据，除非是where条件筛选过滤！！！！


所以，on a.id=b.id and a.id=1 的意思是

 a表id 遍历 b表 id 得到以下结果
        
       ---->b.id=1  (符合on中条件a.id=1（因为左边是a.id=1），又因为这条记录a，b表的记录都是1,  
                     也符合了a.id=b.id)
a.id=1  
       ---->b.id=2  (虽然符合了a.id=1, 但是a.id=1,b.id=2 所以不符合on条件 a.id=b.id,
                     所以这条记录不显示出来)


        ----->b.id=1 (不符合a.id=1)

a.id=2 
        ----->b.id=2 （不符合a.id=1）

虽然a.id=2中的两个不符合  但是 left join 会取所有记录，所以显示a.id=2 和b表中的记录为null ，只显示一条记录。
                   

题目三：

3、

select * from  a left join b

on a.id = 1 

解析：
注意！！！！
left join 取左边表的所有数据，除非是where条件筛选过滤

a,b表中 on条件没有直接的关联，只是通过join把两个表的数据绑定在一起了，所以取笛卡尔积，
然后left join（外连接）保留了a.id=2的记录

虽然a.id=2中的两个不符合  但是 left join 会取所有记录，所以显示a.id=2 和b表中的记录为null ，只显示一条记录。

题目四：

4、

select * from  a ,b

where a.id=b.id

解析： 两个表隐式连接 产生笛卡尔积

再通过where条件筛选

题目五：

5、

select * from a,b

解析： 返回两个表的笛卡尔积

4、总结：

对于left join，不管on后面跟什么条件，左表的数据全部查出来，因此要想过滤需把条件放到where后面
对于inner join，满足on后面的条件表的数据才能查出，可以起到过滤作用。也可以把条件放到where后面
在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里