ORD_ID CUST_ID ORD_DATE ORDER_TOTAL
------ ------- -------- -----------
   100     100 00-10-17        7500
   101     102 01-05-11        8000
   102     104 02-09-17       12000
   103     103 01-10-21        4500
   104     105 00-06-25        6000
   105     100 02-06-19        5500

【CUSTOMERS】

CUST_ID CUST_NAME
------- ----------
    100 SMITH
    101 ALLEN
    102 WARD
    103 JONES
    104 SMITH

次の文の結果として正しいものを選択しなさい。

SELECT ord_id, cust_id, ord_date FROM orders
WHERE cust_id = (SELECT cust_id FROM customers WHERE cust_name='SMITH');


    a．
          ORD_ID    CUST_ID ORD_DATE
      ---------- ---------- --------
             105        100 02-06-19
             100        100 00-10-17
             102        104 02-09-17
	  
    b．
          ORD_ID    CUST_ID ORD_DATE
      ---------- ---------- --------
             105        100 02-06-19
             100        100 00-10-17
	  			
    c．
          ORD_ID    CUST_ID ORD_DATE
      ---------- ---------- --------
             102        104 02-09-17
			
    d．エラーとなる

正解：d

■解説

　前回の宿題とした問題です。副問い合わせとの比較演算子に「=」を使用する場合、副問い合わせが1行を戻す必要があります。資料であるCUSTOMERS表には、副問い合わせで使用しているCUST_NAME列が「SMITH」のレコードが2行ありますので、実行時にエラーとなります（正解d）。

　選択肢aの結果は、「IN」演算子を使用すれば出力できます。

SELECT ord_id, cust_id, ord_date FROM orders
WHERE cust_id IN (SELECT cust_id FROM customers WHERE cust_name='SMITH');

　選択肢bと選択肢cの結果を出力するには、条件が必要ですね。1つの方法として、副問い合わせ内でグループ関数を使用するというものがあります。

■選択肢b：

SELECT ord_id, cust_id, ord_date FROM orders
WHERE cust_id = (SELECT MIN(cust_id) FROM customers WHERE cust_name='SMITH');

■選択肢c：

SELECT ord_id, cust_id, ord_date FROM orders
WHERE cust_id = (SELECT MAX(cust_id) FROM customers WHERE cust_name='SMITH');

3．複数行副問い合わせ

　結果として複数行を戻す副問い合わせは「複数行副問い合わせ」と呼ばれます。複数行副問い合わせでは、複数行比較演算子（IN、ANY、ALL）を使用します。


IN	副問い合わせの結果行のいずれかと等しい
ANY	副問い合わせの結果行のいずれかと比較する（演算子　ANY（副問い合わせ））
ALL	副問い合わせの結果行のすべてと比較する（演算子　ALL（副問い合わせ））

　いずれかの値と比較するという意味ではINとANYは同じですが、INは＝で比較するのに対し、ANYは指定した演算子で、つまり範囲で比較することもできる点が異なります。

問題

■問題1

次の資料を確認してください。

【ORDERS】

ORD_ID CUST_ID ORD_DATE ORDER_TOTAL
------ ------- -------- -----------
   100     100 00-10-17        7500
   101     102 01-05-11        8000
   102     104 02-09-17       12000
   103     103 01-10-21        4500
   104     105 00-06-25        6000
   105     101 02-06-19        5500
   106     104 01-04-12        9000
   107     103 02-11-15        8000

【CUSTOMERS】

CUST_ID CUST_NAME  COUN
------- ---------- ----
    100 SMITH      LA
    101 ALLEN      CA
    102 WARD       LA
    103 JONES      FR
    104 MARTIN     US
    105 BLAKE      IT

次の文の結果として正しいものを選択しなさい。

SELECT ord_id, cust_id, ord_date, order_total
FROM   orders
WHERE  order_total > ANY (SELECT order_total
                          FROM   orders
                          WHERE  cust_id IN (SELECT cust_id
                                             FROM   customers
                                 WHERE  country IN ('LA', 'IT')));


    a．
      ORD_ID CUST_ID ORD_DATE ORDER_TOTAL
      ------ ------- -------- -----------
         104     105 00-06-25        6000
	  
    b．
      ORD_ID CUST_ID ORD_DATE ORDER_TOTAL
      ------ ------- -------- -----------
         100     100 00-10-17        7500
         101     102 01-05-11        8000
	  			
    c．
      ORD_ID CUST_ID ORD_DATE ORDER_TOTAL
      ------ ------- -------- -----------
         100     100 00-10-17        7500
         101     102 01-05-11        8000
         102     104 02-09-17       12000
         104     105 00-06-25        6000
         106     104 01-04-12        9000
         107     103 02-11-15        8000
	  			
    d．
      ORD_ID CUST_ID ORD_DATE ORDER_TOTAL
      ------ ------- -------- -----------
         100     100 00-10-17        7500
         101     102 01-05-11        8000
         102     104 02-09-17       12000
         106     104 01-04-12        9000
         107     103 02-11-15        8000
			
    e．エラーとなる

正解：d

■解説

　この問題のように副問い合わせが入れ子になっている場合は、一番内側の副問い合わせから考えていきましょう。

問い合わせ1 WHERE（問い合わせ2 WHERE（問い合わせ3））

　問い合わせ3の結果を使用して問い合わせ2を行い、その結果を問い合わせ1の条件に使用します。

（1）問い合わせ3

SELECT cust_id
FROM customers
WHERE country IN ('LA', 'IT')

この結果、CUST_IDが100、102、105の値が戻ります。

（2）問い合わせ2

　問い合わせ3の結果を使うと、次のようになります。

SELECT order_total
FROM orders
WHERE cust_id IN (100, 102, 105)

この結果、ORDER_TOTALが7500、8000、6000の値が戻ります。

（3）問い合わせ1

SELECT ord_id, cust_id, ord_date, order_total
FROM orders
WHERE order_total > ANY (...)

　ANY演算子は、問い合わせ2の結果のいずれかとの比較をします。7500、8000、6000のいずれかの値より大きなORDER_TOTAL列値を持つ行を求めるので、「ORDER_TOTAL列が6000より大きいORDERS表の行」が答えとなります。演算子として「>」を使用していますので、6000と等しい値は含まれません。正解はdです。

4．トップN分析

　FROM句に副問い合わせを使用し、副問い合わせ内でORDER BY句を使用すると、ソート済み結果の集合を作成することができます。主問い合わせ側でROWNUM疑似列を使用し、取得したい行を限定すれば、上位N個のデータを戻すことができます。

SELECT 列名リスト FROM (SELECT 列名リスト FROM 表名 [WHERE 条件式] ORDER BY ソート列)
WHERE ROWNUM <= 取得行数;

・ORDER BY：トップNとして取得する基準を指定する。下位N個を取得するならASCソート、上位N個を取得するならDESCソートを使用する

・ROWNUM：結果の集合に連番を振る疑似的な列。<、<=を使用して取得行数を記述する

　ROWNUM疑似列は、現在の表内の格納順序どおりに行に連番を振ってしまいます。上位N個、下位N個を取得するには副問い合わせでソートしておくことが必要です。

例：
SQL> SELECT empno, ename, sal FROM (SELECT empno, ename,
                           sal FROM emp ORDER BY sal DESC)
  2  WHERE  ROWNUM <= 3;
      
     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5000
      7788 SCOTT            3000
      7902 FORD             3000

5．集合演算子

　リレーショナルデータベースでは、数学の集合論を使用して必要な行を戻すことが可能です。Oracleデータベースでは、UNION、INTERSECT、MINUS集合演算子を使用して集合を決定します。


UNION	各問い合わせで選択されたすべての行を戻す
INTERSECT	両方の問い合わせで選択された行を戻す
MINUS	1つ目の問い合わせで選択された行から、2つ目の問い合わせで選択された行を除く

　いずれの集合演算子も重複値を排除した結果を戻します。UNION ALLという集合演算子だけは例外で、重複値もそのまま戻します。

図1　集合演算子

　図1の2つの○が異なる問い合わせと考えましょう。左側の集合にはA、A、B、Cの4つの値が、右側の集合にはA、C、D、Eの4つの値が格納されています。重なっているのは、両方の集合に存在している値です。集合演算子はそれぞれ次の値を戻します。

UNION：A、B、C、D、E
UNION ALL：A、A、B、C、A、C、D、E
INTERSECT：A、C
MINUS：B

まとめ

　前回と今回の2回にわたって、副問い合わせによって問い合わせ結果をSQL文の条件に使用する方法を解説しました。次の内容をチェックしておきましょう。

副問い合わせは、ほかの問い合わせ結果に基づく問い合わせ
単一行比較演算子（=、、>=、!=）を使用した場合、1行と比較できる。複数行戻るとエラー
複数行比較演算子（IN、ANY、ALL）を使用すれば複数行と比較できる

宿題

　次回は、「データ操作」を確認します。次の宿題を解いておいてください。

トランザクションを完了する文を2つ選択しなさい。

a．DELETE FROM emp;
b．ROLLBACK TO SAVEPOINT a;
c．GRANT SELECT ON emp TO hr;
d．ALTER TABLE emp ADD COLUMN jobs VARCHAR2(10);
e．SELECT empno, ename FROM emp ORDER BY sal;

「次回」へ

「ORACLE MASTER Bronze　SQL基礎I 講座」バックナンバー