副問い合わせの構文を覚えるORACLE MASTER Bronze SQL基礎I 講座(9)

Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう!(参考記事:「Oracle 10g対応のORACLE MASTERとは?」)

» 2006年01月13日 00時00分 公開

 前回に引き続き、副問い合わせによって問い合わせ結果をSQL文の条件に使用する方法について学びます。前回「SELECT文中のSELECT文、副問い合わせ」で、副問い合わせの意味、構文の記述について紹介しました。今回は単一行副問い合わせ、複数行副問い合わせの実行方法を学びましょう。

理解しておきたいこと

1.確認しておきたい内容

  • 単一行副問い合わせの実行
  • 複数行副問い合わせの実行

2.単一行副問い合わせ

 結果として1行を戻す副問い合わせは「単一行副問い合わせ」と呼ばれます。単一行副問い合わせでは、単一行比較演算子(=、<、<=、>、>=、!=)を使用することができます。

 副問い合わせがNULLを戻す場合、主問い合わせの結果は1行も戻されません。この場合「WHERE列 = NULL」を指定したことになりますが、NULL値は「=」では比較できないためです。

問題

■問題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     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 集合演算子

 図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;


Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。