Oracle SQL SELECT4 : 集合演算子 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行が選択されました。

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行しか返されません。

尚、以下の様に3個の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行が選択されました。

SQL 第2版 ゼロからはじめるデータベース操作【電子書籍】[ ミック ]

プロとしてのOracle PL/SQL入門 【第3版】(Oracle 12c、11g、10g対応)【電子書籍】[ アシスト教育部 ]



ページのトップへ戻る