連載
» 2005年10月07日 00時00分 公開

ORACLE MASTER Bronze SQL基礎I 講座(6):SQLで複数の表からデータを取り出す

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

[有限会社 G.F.インフィニティ,Project - ∞]

 前回「SQLの関数でデータを集計する」で、グループ関数を紹介しました。今回と次回の2回にわたって、複数の表からデータを表示する方法について学びましょう。

理解しておきたいこと

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

  • 等価結合と非等価結合を使用し、複数の表へアクセスするSELECT文の記述
  • グループ関数の使用
  • 外部結合による、結合条件に一致しないデータの表示
  • 自己結合を使用した1つの表での結合

2.結合のタイプ

 SELECT文で結合を使用することで、1つの表または複数の表をリンクさせ、1つの結果として戻すことができます。

 結合はいろいろな角度から分類されます。

  • 結合する条件による分類
等価結合 結合条件によって、特定の列の値が等しいものを結び付ける
非等価結合 結合条件によって、特定の列の値が特定の列の範囲内であるものを結び付ける

 結合条件を満たすデータを戻すかどうかで、内部結合と外部結合に分類されます。

内部結合 結合条件を満たすデータだけを戻す
外部結合 結合条件を満たさないデータも含めて戻す

問題

■問題1

非等価結合を使用するのに適した状況を2つ選択しなさい。

a.結合する列に同じ値が存在しない
b.値の範囲で結合する
c.主キーと外部キーの関係がある
d.3つ以上の表を結合する

正解:a、b

■解説

 前回の宿題にした問題です。非等価結合は、等価記号(=)ではなく、範囲(BETWEEN、<、<=、>、>=)で結び付けるものです。非等価結合が必要となるのは次のような場合です。

  • 結合する列に同じ値が存在しない
  • 値の範囲で結合する

 結合列に同じ値がある場合は等価結合を使用しますが、同じ値が存在しない場合(正解a)や、範囲で結び付ける場合(正解b)は非等価結合を使用します。

 選択肢c選択肢dは、等価結合を使用する場合です。

■問題2

等価結合の特徴を選択しなさい。

a.2つの表からデータを取得する
b.2つの列からデータを取得する
c.主キーと外部キーが構成されている表からデータを取得する
d.n-1の結合条件を使用してn個の表からデータを取得する

正解:d

■解説

 等価結合は、等価記号(=)で結び付ける結合です。正解dのように、例えば3つの表を結合するのであれば、2つの結合条件を使用します。非等価結合の結合条件の数は範囲によりますので、n-1とは限りません。

 選択肢a選択肢bのように、結合する表や列の数を限定されることはありません。選択肢cのように、主キーと外部キーによる外部キー制約のある結合は多いですが、必須条件ではありません。

3.内部結合

 内部結合では、結合構文の違いによって、以下の結合タイプが使用できます。

クロス結合 2つの表のすべての組み合わせを作成する。直積、デカルト積とも呼ばれる
自然結合 2つの表の同じ名前の列すべてを、自動的に等価結合の結合条件として使用する
USING句 2つの表の同じ名前の列のうち、USING句で指定した列のみを等価結合の結合条件として使用する
ON句 2つまたは1つの表から、ON句で指定した結合条件を満たすもののみを戻す
自己結合 1つの表から結合条件を満たすもののみを戻す。ON句を使用する

 それぞれの結合構文は以下のとおりです。これらはSQL1999構文と呼ばれ、ANSIで規格化されています。

・クロス結合

 SELECT 列名リスト FROM 表名1 CROSS JOIN 表名2;

例:
SQL> SELECT empno, ename, emp.deptno FROM emp CROSS JOIN dept
  2  ORDER BY empno;
      
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7369 SMITH              20
      7369 SMITH              20
      7369 SMITH              20
      7499 ALLEN              30
      7499 ALLEN              30
      7499 ALLEN              30
‥‥

 クロス結合では2つの表のすべての組み合わせが戻されます。例えば、emp表14行、dept表4行のクロス結合では14×4=56行が戻されます。一般的にクロス結合の結果をそのまま必要とする業務はほとんどなく、無意味な結合といえますが、負荷テストなどに使用されます。

・自然結合

 SELECT 列名リスト FROM 表名1 NATURAL JOIN 表名2;

 2つの表の同じ名前の列は、同じデータ型である必要があります。異なるデータ型の同名列がある場合はエラーが発生します。

例:
SQL> SELECT empno, ename, deptno, dname FROM emp NATURAL JOIN
     dept
  2  WHERE  deptno = 10;
      
     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7782 CLARK              10 ACCOUNTING
      7839 KING               10 ACCOUNTING
      7934 MILLER             10 ACCOUNTING

・USING句

 SELECT 列名リスト FROM 表名1 JOIN 表名2 USING(結合列);

例:
SQL> SELECT empno, ename, deptno, dname FROM emp JOIN dept
     USING(deptno)
  2  WHERE  deptno = 10;
      
     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7782 CLARK              10 ACCOUNTING
      7839 KING               10 ACCOUNTING
      7934 MILLER             10 ACCOUNTING

 この例ではNATURAL JOIN句を使用した自然結合と変わりませんが、2つの表に2つ以上同じ列名のものがあり、そのうちの一部の列のみを結合条件にしたい場合はUSING句を使用します。

・ON句

 SELECT 列名リスト FROM 表名1 JOIN 表名2 ON(結合条件);

 ON句の結合条件では、どの表から取得するのかを明確に指定するため、表名または表名の別名を使って「表名1.列 = 表名2.列」のように条件を記述します。このように表名または表別名による接頭辞を付けることを、列名の修飾と呼びます。

 ON句では等価条件だけでなく、非等価条件を記述することもできます。

例:
SQL> SELECT e.empno, e.ename, s.grade FROM emp e JOIN
     salgrade s
  2  ON     (e.sal BETWEEN s.losal AND s.hisal)
  3  WHERE  e.deptno = 10;
      
     EMPNO ENAME           GRADE
---------- ---------- ----------
      7839 KING                5
      7782 CLARK               4
      7934 MILLER              2

・自己結合

 SELECT 列名リスト FROM 表名1 別名1 JOIN 表名1 別名2 ON(結合条件);

 1つの表を別名を利用して2つの表に見せ掛け、結合することができます。

例:
SQL> SELECT e.empno, e.ename, m.empno, e.ename mname
  2  FROM   emp e JOIN emp m
  3  ON     (e.mgr = m.empno)
  4  WHERE  e.deptno = 10;
      
     EMPNO ENAME           EMPNO MNAME
---------- ---------- ---------- ----------
      7782 CLARK            7839 CLARK
      7934 MILLER           7782 MILLER

 それぞれの結合では結合条件のほか、WHERE句を使用した行の制限を行うことも可能です。

 表別名はON句だけでなく、自然結合やUSING句でも使用できますが、この場合は2つの表で同じ列名のものに関しては列名の修飾はできません。列名が同じでも修飾ができるのは、ON句を使用した場合のみです。

 

問題

■問題1

USING句を使用するのに適した状況を2つ選択しなさい。

a.異なるデータ型を持つ同じ列名のある表の結合
b.すべて同じ列名を持つ表の結合
c.NULL値を結合
d.非等価結合
e.同じ列名のうち、一部の列を使用して結合

正解:a、e

■解説

 2つの表に複数の同じ列名が存在すると、NATURAL JOIN句では同じ名前の列すべてを結合条件と見なします。

 複数の同じ列名があり、そのうち一部の列のみを結合条件にするには、USING句を使用します(正解e)。同じ名前でデータ型が異なる列が存在する場合も、NATURAL JOIN句でエラーとなるのを防ぐため、USING句を使用して除外します(正解a)。

 そのほかの選択肢の不正解の理由は次のとおりです。

選択肢b:すべて同じ列名を持つ表の場合は、NATURAL JOIN句の使用が適切です。

選択肢c:NULL値を結合するということは、結び付ける値がないわけですから、外部結合の使用が適切です。

選択肢d:USING句とNATURAL JOIN句では非等価結合はできず、等価結合のみ行われます。非等価結合を行うには、ON句を使用します。

■問題2

次のEMP表とSAL_GRADE表のデータを確認してください。

EMP表
EMPNO ENAME      JOB          MGR HIREDATE   SAL  COMM DEPTNO
----- ---------- ---------- ----- -------- ----- ----- ------
 7369 SMITH      CLERK       7902 80-12-17   800           20
 7499 ALLEN      SALESMAN    7698 81-02-20  1600   300     30
 7521 WARD       SALESMAN    7698 81-02-22  1250   500     30
 7566 JONES      MANAGER     7839 81-04-02  2975           20
 7654 MARTIN     SALESMAN    7698 81-09-28  1250  1400     30
 7698 BLAKE      MANAGER     7839 81-05-01  2850           30
 7782 CLARK      MANAGER     7839 81-06-09  2450           10
 7788 SCOTT      ANALYST     7566 87-04-19  3000           20
 7839 KING       PRESIDENT        81-11-17  5000           10
 7844 TURNER     SALESMAN    7698 81-09-08  1500     0     30
 7876 ADAMS      CLERK       7788 87-05-23  1100           20
 7900 JAMES      CLERK       7698 81-12-03   950           30
 7902 FORD       ANALYST     7566 81-12-03  3000           20
 7934 MILLER     CLERK       7782 82-01-23  1300           10
SAL_GRADE表
    DEPTNO      LOSAL      HISAL
---------- ---------- ----------
        10        700       1200
        20       1201       1400
        30       1401       2000
        40       2001       3000
        50       3001       9999

SMITHが所属する部門の最小給与から最大給与の範囲内の給与を受け取っている従業員を表示している文を選択しなさい。


    a.SELECT empno, ename, sal
       FROM   emp NATURAL JOIN sal_grade
       WHERE  deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
	   
    b.SELECT empno, ename, sal
       FROM   emp e JOIN sal_grade s
       ON     (e.sal BETWEEN s.losal AND s.hisal)
       WHERE  s.deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
	   
    c.SELECT empno, ename, sal
       FROM   emp e JOIN sal_grade s
       WHERE  s.deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');
	   
    d.SELECT empno, ename, sal
       FROM   emp e JOIN sal_grade s
       USING  (e.sal BETWEEN s.losal AND s.hisal)
       WHERE  s.deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');

正解:b

正解:b

■解説

 EMP表とSAL_GRADE表は部門番号で結び付けられるようです。SAL_GRADE表から取得できる値は最小給与(LOSAL)と最大給与(HISAL)です。

 ユーザーSMITHの部門番号は20です。SAL_GRADE表のLOSALは1201、HISALは1400が該当します。この範囲の給与を受け取っている従業員を求めるには、正解bのSELECT文が適切です。

SQL> SELECT empno, ename, sal
  2  FROM   emp e JOIN sal_grade s
  3  ON     (e.sal BETWEEN s.losal AND s.hisal)
  4  WHERE  s.deptno = (SELECT deptno FROM emp
  5                     WHERE  ename = 'SMITH');
      
EMPNO ENAME        SAL
----- ---------- -----
 7521 WARD        1250
 7654 MARTIN      1250
 7934 MILLER      1300

 この問題のような任意の条件を記述するには、USING 〜ON句を使用します。選択肢cでは、ON句が含まれていないため構文エラーとなります。選択肢dのUSING句では、結合条件となる列名のみ記述できます。条件句を記述すると構文エラーになります。

 選択肢aで使用しているNATURAL JOIN句では、自然結合ですので同じ名前の列で等価結合を行います。この文ではWHERE句による部門番号のみが条件となり、SAL_GRADE表の範囲では戻されていません。結果としては部門番号20のレコードのみが表示されます。

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800
      7566 JONES                      2975
      7788 SCOTT                      3000
      7876 ADAMS                      1100
      7902 FORD                       3000

■問題3

次のデータを確認してください。

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7566 JONES            7839       2975
      7654 MARTIN           7698       1250
      7782 CLARK            7839       2450
      7788 SCOTT            7566       3000
      7839 KING                        5000
      7902 FORD             7566       3000

次のSELECT文の結果を選択しなさい。

SELECT e.empno, e.ename, m.empno, m.ename
FROM emp e JOIN emp m
ON (e.mgr = m.empno)
WHERE e.sal > 2500;


    a.
           EMPNO ENAME           EMPNO ENAME
      ---------- ---------- ---------- ----------
            7902 FORD             7566 JONES
            7788 SCOTT            7566 JONES
            7566 JONES            7839 KING
	  
    b.
           EMPNO ENAME           EMPNO ENAME
      ---------- ---------- ---------- ----------
            7902 FORD             7566 JONES
            7788 SCOTT            7566 JONES
            7782 CLARK            7839 KING
            7566 JONES            7839 KING
			
    c.
           EMPNO ENAME           EMPNO ENAME
      ---------- ---------- ---------- ----------
            7902 FORD             7566 JONES
            7788 SCOTT            7566 JONES
            7782 CLARK            7839 KING
            7566 JONES            7839 KING
            7839 KING
            7654 MARTIN
			
    d.
           EMPNO ENAME           EMPNO ENAME
      ---------- ---------- ---------- ----------
            7902 FORD             7566 JONES
            7788 SCOTT            7566 JONES
            7782 CLARK            7839 KING
            7566 JONES            7839 KING
            7839 KING             7839 KING
            7654 MARTIN           7654 MARTIN

正解:a

■解説

 自己結合の問題です。今回の問題では、「sal > 2500」が含まれていますので、元データのうち次の4レコードのみが結果に使用されることになります。

     EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ----------
      7566 JONES            7839       2975
      7788 SCOTT            7566       3000
      7839 KING                        5000
      7902 FORD             7566       3000

 このうち、MGR列に値のないEMPNO列7839のレコードは、外部結合を使用しなければ結合できません。そのため結合結果は、EMPNO列7566、7788、7902の3レコードのみが含まれる選択肢aとなります。

 そのほかの選択肢の不正解の理由は次のとおりです。

選択肢b:

次のようにsal列の値を限定しない場合の結果です。

SELECT e.empno, e.ename, m.empno, m.ename
FROM emp e JOIN emp m
ON (e.mgr = m.empno);

選択肢c:

次のように外部結合を使用した場合の結果です。

SELECT e.empno, e.ename, m.empno, m.ename
FROM emp e LEFT OUTER JOIN emp m
ON (e.mgr = m.empno);

選択肢d:

結び付けるもの(mgr)がない場合、自身の値を入れています。この結果を出す1つの方法として、次の文が使用できます。

SELECT e.empno, e.ename,
DECODE(m.empno, NULL, e.empno, m.empno) empno,
DECODE(m.ename, NULL, e.ename, m.ename) ename
FROM emp e LEFT OUTER JOIN emp m
ON (e.mgr = m.empno);

宿題

 次回は、今回に引き続き複数の表からデータを表示する方法について確認します。次の宿題を解いておいてください。

外部結合を使用する処理を2つ選択しなさい。

a.両方にNULL値が含まれている
b.片方に含まれないデータも取得する必要がある
c.両方に含まれるデータも含まれないデータも取得する必要がある
d.両方に含まれるデータのみ取得する必要がある
e.主キーと外部キーの関係がある表からデータを取得する


Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

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

メールマガジン登録

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