Oracle SQL SELECT４ : 集合演算子 UNION,INTERSECT,MINUS
集合演算子は2個の問い合わせ結果を1個の結果に結合する演算子です。以下に集合演算子を示します。
- 各々の問い合わせ結果の全ての行で、重複行は含まないものを返す。
- 各々の問い合わせ結果の全ての行で、重複行は含む。
- 集合演算子のソートについて
- 各々の問い合わせ結果で共に存在する行で、重複行は含まない。
- 最初の問い合わせ結果の行から、次の問い合わせで返る行を含まないもの。重複行は含まない。
これらの演算子の説明をする為に、これまで使用してきたテーブルTM_商品を例にとって説明していきます。以下にテーブルの内容一覧を全て表示します。
SQL> SELECT * FROM TM_商品;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
1 PC-9001 デスクトップパソコン 65000 98000
2 PC-9002 デスクトップパソコン 95000 120000
3 PC-9003 デスクトップパソコン 150000 190000
4 NOTE-1010 ノートパソコン 125000 188000
5 NOTE-1020 ノートパソコン 145000 200000
6 NOTE-1030 ノートパソコン 155000 220000
7 PRT-3001 プリンタ 45000 88000
8 PRT-4001 プリンタ 115000 180000
9 CRT-1001 ディスプレイ 45000 78000
10 CRT-2001 ディスプレイ 55000 98000
11 HUB-A001 ネットワーク 20000 40000
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
12 HUB-B001 ネットワーク 40000 60000
12行が選択されました。
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
UNION (各々の問い合わせ結果の全ての行で、重複行は含まないものを返す。)
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
2 UNION
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
7 PRT-3001 プリンタ 45000 88000
9 CRT-1001 ディスプレイ 45000 78000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
1行目のSELECT文は仕入単価が50,000円未満の商品を全て結果として返すものであり、商品コードが7,9,11,12です。また3行目のSELECT文は仕入単価が40,000円未満の商品を選択し、商品コードが11となります。商品コードが11は重複行となり、結果として上記の4行しか返されません。
尚、以下の様に３個のSELECT文をUNIONで結合した例を示します。5行目のSELECT文は固定値としてのデータを結果に含める為のもので、SELECT句に並べるデータの個数は1行目及び3行目で返される列の個数に合わせなければなりません。
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
2 UNION
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000
4 UNION
5 SELECT 0,'ダミー商品名','ダミー商品区分',1000,1000 FROM DUAL;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
0 ダミー商品名 ダミー商品区分 1000 1000
7 PRT-3001 プリンタ 45000 88000
9 CRT-1001 ディスプレイ 45000 78000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
列の個数を合わせなかった例を以下に示します。
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000 2 UNION 3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000 4 UNION 5 SELECT 0,'ダミー商品名','ダミー商品区分',1000 FROM DUAL; SELECT * FROM TM_商品 WHERE 仕入単価 < 40000 * エラー行: 3: エラーが発生しました。 ORA-01789: 問合せブロックにある結果の列数が正しくありません
UNION ALL （各々の問い合わせ結果の全ての行で、重複行は含む。)
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
2 UNION ALL
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
7 PRT-3001 プリンタ 45000 88000
9 CRT-1001 ディスプレイ 45000 78000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
11 HUB-A001 ネットワーク 20000 40000
UNIONの例のSQLをUNION ALLに変更すると、UNIONとの違いがはっきりすると思います。結果として返される商品コードが7,9,11,12は最初のSELECT文での結果の行であり、最後の商品コードが11が2番目のSELECT文の結果の行を示しています。
ソートについて
ORDER BY句により返される結果をソートする場合は、結果の列の名称ではなく列の位置を指定しなければなりません。以下に商品コード及び、仕入単価でソートする様子を示します。尚、最後のエラーの発生しているSQL文はORDER BY句に列名を指定したものを示しています。
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
2 UNION ALL
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000
4 ORDER BY 1;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
7 PRT-3001 プリンタ 45000 88000
9 CRT-1001 ディスプレイ 45000 78000
11 HUB-A001 ネットワーク 20000 40000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
2 UNION ALL
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000
4 ORDER BY 4;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
11 HUB-A001 ネットワーク 20000 40000
11 HUB-A001 ネットワーク 20000 40000
12 HUB-B001 ネットワーク 40000 60000
7 PRT-3001 プリンタ 45000 88000
9 CRT-1001 ディスプレイ 45000 78000
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
2 UNION ALL
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000
4 ORDER BY 商品区分;
ORDER BY 商品区分
*
エラー行: 4: エラーが発生しました。
ORA-00904: 列名が無効です。
エラーが発生するORDER BY句の列名指定も以下の様に、副問い合わせにすれば可能になります。
SQL> SELECT * FROM
2 (
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 50000
4 UNION
5 SELECT * FROM TM_商品 WHERE 仕入単価 < 40000
6 UNION
7 SELECT 0,'ダミー商品名','ダミー商品区分',1000,1000 FROM DUAL
8 )
9 ORDER BY 商品コード DESC;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
12 HUB-B001 ネットワーク 40000 60000
11 HUB-A001 ネットワーク 20000 40000
9 CRT-1001 ディスプレイ 45000 78000
7 PRT-3001 プリンタ 45000 88000
0 ダミー商品名 ダミー商品区分 1000 1000
INTERSECT （各々の問い合わせ結果で共に存在する行で、重複行は含まない。）
INTERSECTの例を以下に示します。この演算子は論理演算で言うところのAND演算の様な働きをします。1行目の仕入単価が40,000円より高いものと、3行目の仕入単価が50,000円未満のものとのANDをとっています。
SQL> SELECT * FROM TM_商品 WHERE 仕入単価 > 40000
2 INTERSECT
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 50000;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
7 PRT-3001 プリンタ 45000 88000
9 CRT-1001 ディスプレイ 45000 78000
MINUS （最初の問い合わせ結果の行から、次の問い合わせで返る行を含まないもの。重複行は含まない。）
MINUSの例を以下に示します。この演算子は最初のSELECT文の結果から、次のSELECT文の結果を差し引く様な働きをします。1行目はTM_商品の全てを返し、その結果から3行目の仕入単価が50,000円未満の商品を省く結果となります。
SQL> SELECT * FROM TM_商品
2 MINUS
3 SELECT * FROM TM_商品 WHERE 仕入単価 < 50000;
商品コード 商品名 商品区分 仕入単価 売上単価
---------- ---------------------------------------- -------------------- ---------- ----------
1 PC-9001 デスクトップパソコン 65000 98000
2 PC-9002 デスクトップパソコン 95000 120000
3 PC-9003 デスクトップパソコン 150000 190000
4 NOTE-1010 ノートパソコン 125000 188000
5 NOTE-1020 ノートパソコン 145000 200000
6 NOTE-1030 ノートパソコン 155000 220000
8 PRT-4001 プリンタ 115000 180000
10 CRT-2001 ディスプレイ 55000 98000
8行が選択されました。
