集合演算

• MySQL

(8.0.31)

https://sakaik.hateblo.jp/entry/20221011/mysql8031_supports_intersect_and_except

※ (8.0.31)よりexceptとintersectが使用可能

drop table tab1;
drop table tab2;
create table tab1(col1 int);
create table tab2(col1 int);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab2 values(1);
insert into tab2 values(3);
select * from tab1;
select * from tab2;

select col1 from tab1
union
select col1 from tab2
;

select col1 from tab1
union all
select col1 from tab2
;

select col1 from tab1
except
select col1 from tab2
;

※(8.0.30)以前
select T1.col1
from tab1 T1
left join tab2 T2
on T1.col1 = T2.col1
where T2.col1 is null
;

select col1 from tab1
intersect
select col1 from tab2
;

※(8.0.30)以前
select T1.col1
from tab1 T1
inner join tab2 T2
on T1.col1 = T2.col1
;

• Oracle

(12cR1)
(12cR2)
(18c)
(19c)

drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int);
create table tab2(col1 int);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab2 values(1);
insert into tab2 values(3);
select * from tab1;
select * from tab2;
commit;

select col1 from tab1
union
select col1 from tab2
;

select col1 from tab1
union all
select col1 from tab2
;

select col1 from tab1
minus
select col1 from tab2
;

select col1 from tab1
intersect
select col1 from tab2
;

• PostgreSQL

(9.4)
(9.6)
(10)
(11)
(12)

drop table tab1;
drop table tab2;
create table tab1(col1 int);
create table tab2(col1 int);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab2 values(1);
insert into tab2 values(3);
select * from tab1;
select * from tab2;

select col1 from tab1
union
select col1 from tab2
;

select col1 from tab1
union all
select col1 from tab2
;

select col1 from tab1
except
select col1 from tab2
;

select col1 from tab1
intersect
select col1 from tab2
;

• SQL Server

(2014)
(2016)
(2017)
(2019)

drop table tab1;
drop table tab2;
create table tab1(col1 int);
create table tab2(col1 int);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab2 values(1);
insert into tab2 values(3);
select * from tab1;
select * from tab2;

select col1 from tab1
union
select col1 from tab2
;

select col1 from tab1
union all
select col1 from tab2
;

select col1 from tab1
except
select col1 from tab2
;

select col1 from tab1
intersect
select col1 from tab2
;