MINUS,INTERSECT和UNION和UNION ALL 详解
MINUS,INTERSECT关键字 可以通过 in not in esit等实现,那么为什么还需要用到 MINUS,INTERSECT呢?
有人做过实验:made_order共23万笔记录,charge_detail共17万笔记录:
SELECT order_id FROM made_order
MINUS
SELECT order_id FROM charge_detail
耗时:1.14 sec
SELECT a.order_id FROM made_order a
WHERE a.order_id NOT exists (
SELECT order_id
FROM charge_detail
WHERE order_id = a.order_id
)
耗时:18.19 sec
性能相差15.956倍!
MINUS(减去):
用集合表示如下 A={1,2,3,4,5} B={1,2,5}
select a MINUS b =>c{3,4}
select aa.id from aa MINUS select bb.id from bb;
可以理解为相当于 select aa.id from aa where aa.id not in (select bb.id from bb);
比如 aa,bb表数据如下:(aa中id为1-8,bb中为 1-4)
select aa.id from aa where aa.id not in (select bb.id from bb); 结果为5-8
INTERSECT(交集):
用集合表示如下 A={1,2,3,4,5} B={1,2,5}
select a INTERSECT b =>c{1,2,5}
select aa.id from aa INTERSECTselect bb.id from bb;
可以理解为相当于 select aa.id from aa where aa.id in (select bb.id from bb);
select aa.id from aa INTERSECTselect bb.id from bb;结果为1-4
UNION 和UNION ALL区别
UNION (并集):
用集合表示如下 A={1,2,3,4,5} B={1,2,5,6}
select a union b =>c{1,2,3,4,5,6}
select * from aa union select * from bb;
可以理解为相当于
(1)先查询aa表所有数据,select aa.id from aa;
(2)再查询bb表中没有id为aa表的数据 select bb.id from bb where not exists (select aa.id from aa where aa.id=bb.id);
(3)结果集=(1)+(2)
比如 bb表中加入一个id为10的记录
select * from aa union select * from bb; 结果为 aa表中的1-8,bb表中的10
UNION ALL(并集+公共部分):
用集合表示如下 A={1,2,3,4,5} B={1,2,5,6}
select a union all b =>c{1,2,3,4,5,1,2,5,6}
select * from aa union all select * from bb;
可以理解为相当于
(1)先查询aa表所有数据,select aa.id from aa;
(2)再查询bb表的数据 select bb.id from bb;
(3)结果集=(1)+(2)
select * from aa union all select * from bb; 结果为 aa表中的1-8,bb表中的1-4,10