オラクルのdual表を用いて一時的な表を二つ作成し、集合演算子の確認をする。

SQL

select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual

実行結果

	 2	    3
---------- ----------
	 2	    3
	 3	    4
	 4	    5
	 5	    6

SQL

select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5

以上２つのクエリを用いて、以下にunion, union all, intersect, exceptの実行結果を示す。PostgreSQLではminusについてexceptを用いる。

1.union
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
union
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5
	 5	    6

2.union all
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
union all
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2
	 2	    3
	 3	    4
	 4	    5
	 2	    3
	 3	    4
	 4	    5
	 5	    6

3.intersect
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
intersect
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 2	    3
	 3	    4
	 4	    5

4.minus
SQL

(select 1,2 from dual
union all
select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
)
minus
(select 2,3 from dual
union all
select 3,4 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
)

実行結果

	 1	    2
---------- ----------
	 1	    2