select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
union all
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
right join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
and t1.name is null ;
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
union all
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
right join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
where t1.name is null ;
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2on t1.name = t2.nameand t1.name is nullunion all
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
right join
(select * from student_table where sex = '女')t2
on t1.name = t2.name ;
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
left join
(select * from student_table where sex = '女')t2
on t1.name = t2.name
where t1.name is null
union all
select t1.*,t2.*
from (
select * from student_table where sex = '男' ) t1
right join
(select * from student_table where sex = '女')t2
on t1.name = t2.name ;
('1004' , '张三1' ,'2000-08-06' , '男')张三2
('1009' , '李四1', '2000-01-01', '男');李四1
('1010' , '李四2', '2001-01-01', '男')李四1
('1013' , '赵六' ,'2000-09-06' , '男');Null
Right join
Null('1006' , '王五', '2000-08-06' , '女');
张三1('1008' , '张三1', '2002-12-01', '女');
张三1('1012' , '张三2', '2001-12-01', '女');
李四1('1011' , '李四1', '2002-08-06' , '女');
解析:
考察点1、sql执行顺序:from >> on >> join >> where >> group by >> having >> select >> distinct >> unoin >> order by >> limit
考察点2、mysql不支持全外连接,因此可以使用left join 和 right join配合union实现
考察点3、on和where的区别:
on执行时机在表连接时根据嵌套循环算法SNJL(mysql仅支持该连接策略,有优化版本INJL:使用索引优化内层循环效率、BNJL:利用join buffer减少内层循环次数)针对驱动表(数据量较小的,left join一般为左表)的每一条记录,遍历关联表的每一项,将满足连接条件的放入结果集。
where是针对结果集作条件筛选。
A、union后的子表,on条件t1.name = t2.name and t1.name is null,显然没有满足的记录,但是连接类型为right join,因此保留t2全部记录,t1字段为null
该结果与左外连接的结果作并集,显然不是全外连接的结果,错误。C同理。
B正确,union all的第一个子表:左外连接的结果为满足连接的记录和左表中不满足连接条件的记录,第二个子表:通过where将右外连接中不满足连接条件的记录筛出,两者并集即为全外连接;