SQLで複数の表からデータを取り出す：ORACLE MASTER Bronze SQL基礎I 講座（6）
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！（参考記事：「Oracle 10g対応のORACLE MASTERとは？」）
前回「SQLの関数でデータを集計する」で、グループ関数を紹介しました。今回と次回の2回にわたって、複数の表からデータを表示する方法について学びましょう。
ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス
第6回 SQLで複数の表からデータを取り出す
理解しておきたいこと
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表のデータを確認してください。
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 |
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.
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。