SQL专项练习22

-- Mysql中表student_table(id,name,birth,sex)，插入如下记录：

('1001' , '' , '2000-01-01' , '男');
('1002' , null , '2000-12-21' , '男');
('1003' , NULL , '2000-05-20' , '男');
('1004' , '张三' , '2000-08-06' , '男');
('1005' , '李四' , '2001-12-01' , '女');
('1006' , '张三' , '2001-12-02' , '女');

-- 执行
 select t1.name ,t2.name from 
 (select * from student_table where sex = '女') t1 
 left join 
 (select * from student_table where sex = '男') t2 
 on t1.name = t2.name  where t2.name is null 
 union
 select t1.name ,t2.name 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;

结果

李四，null

union

null, ''

null, null

null, NULL

去重后的结果有三行