複数の表からデータを取り出して表示させる(2):Webブラウザで気軽に学ぶ実践SQL講座(8)
前回の連載では、SQLの基本的な結合構文について説明しました。結合構文を使うと、関係する複数の表から、関連するデータを取り出すことができます。今回は、より複雑な、自己結合、外部結合について説明します(編集部)
自己結合
自己結合とは、表をその表自体に結合する構文です。1つの表の中に互いに関係する列があり、その列同士の関係を使ってデータを取得したい場合に使用します。実際の例(図1)で考えてみましょう。
今回使用しているEMP表には、社員の上司の情報を格納したマネージャ列「MGR」が存在します。MGR列を調べることで、社員の上司を検索することができます。
例えば、社員「BLAKE」のMGR列には「7839」という値が格納されていますが、これは上司の社員番号(EMPNO)にあたります。つまり、社員「BLAKE」の上司は、社員番号7839の「KING」であることが分かります。このように、EMP表には、社員(部下)のMGR列には、上司のEMPNOが入っているという関係があります。
このとき、部下「BLAKE」の名前と、上司「KING」の名前を表示するには、どのようなSQL文を記述すればよいでしょうか。
次のように考えると、行き詰ってしまいますね。
これは、部下の名前も、上司の名前も同じEMP表のename列に入っており、区別できないためです。
そこで、前回紹介した表別名をうまく使い、部下用の表(eという別名で識別)と、上司用の表(mという別名で識別)を分けて考えればよいのです(図2)。
このようにすると、「e(部下用のEMP表)のMGR列には、m(上司用のEMP表)のEMONO列が入っている」という関係を表すことができます。
これをもとにSQL文を書いてみましょう。
SELECT e.ename 部下名, m.ename 上司名 FROM emp e JOIN emp m ON e.mgr=m.empno;
少し複雑なので、もう一度整理して考えてみましょう。上記のSQL文は以下のように考えることができます。
SELECT句:「e(部下用のEMP表)」の社員名(つまり部下の社員名)と、「m(上司用のEMP表)」の社員名(つまり上司の社員名)を表示します。
FROM句:部下用のEMP表を「e」、上司用のEMP表を「m」と呼び分けます(ただし、実際の表は1つです)。
ON句:「e(部下)のMGR列には、m(上司)のEMPNOが入っている」という関係を使って結合します。
このように表別名をうまく使うことによって、一見複雑な結合構文も、うまく条件を指定して記述することができます。
内部結合と外部結合
結合する2つの表には、必ずしも一致するデータだけが入っているとは限りません。
例えば、今回使っているEMP表とDEPT表のDEPTNO列を見比べてみると、DEPT表には「40」という部門がありますが、EMP表には、「40」という部門に所属している社員はいません(図4)。今回の連載では、これを「一致しないデータ」と呼びます。
このような場合、今までの結合構文では、両方の表に一致するデータがある行のみが検索されるため、部門40は検索されません(図5)。
SELECT e.ename, d.deptno, d.dname FROM emp e JOIN dept d ON e.deptno=d.deptno;
一致しないデータ(この例では部門40)を含めて検索したい場合には、「外部結合」を使用します。外部結合に対して、今まで説明してきたような、一致するデータのみを検索する結合構文を「内部結合」といいます。
外部結合
では、外部結合を使って、EMP表に存在しない「部門40」を含めて結果データを表示してみましょう。
外部結合では、FROM句に「RIGHT」または「LEFT」キーワードを指定します。「RIGHT」または「LEFT」で、FROM句に記載した表の、左右どちらの表のデータをすべて表示するかを指定します。
例えば、以下の例(図6)ではFROM句の左に「EMP」が、右に「DEPT」が記述されています。右側の「DEPT」表のデータをすべて表示するには、「RIGHT OUTER JOIN」を指定します。図6の結果のように、EMP表に一致するデータがない部門40も含めて、データが表示されます。
SELECT e.ename, d.deptno, d.dname FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;
また、互いに一致するデータがない場合でも、両方の表すべてのデータを表示したい場合には、「FULL OUTER JOIN」を指定します。
コラム---Oracle独自の構文
Oracle Databaseでは、上記で紹介したANSI規格準拠のほかに、Oracle独自の結合構文を使うこともできます。これは、Oracle9iリリース以前のデータベースでは、ANSI規格と異なる独自の構文を使っていたためですが、現在はどちらの構文も使うことができます。どちらの構文を使っても、できることは基本的には同じです。企業ごとののコーディング・ルールに合わせて、使用する構文を選択してください。
ANSI準拠の標準構文とOracle独自の結合構文との比較については、次の表(図7)を参考にしてください。
例えば、Oracle独自の結合構文で「社員名と社員の所属する部門名」を表示するには、以下のように指定します。
SELECT e.ename, d.deptno, d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
筆者紹介
日本オラクル オラクルダイレクト所属。
須々木尚子(すすき なおこ)
オンラインセミナーの講師や、お客様への提案、案件の支援などを担当。著書に「Oracle SQLクイズ」(翔泳社)があります。
- SQLでデータを操作する(副問合せを利用したINSERT/相関UPDATE/MERGE)
- SQLでデータを操作するときの文法(INSERT/UPDATE/DELETE)
- 高度な副問合せの構文
- 副問合せを使った複雑な条件指定
- データをグループ化して表示してみよう(2)
- データをグループ化して表示してみよう(1)
- 複数の表からデータを取り出して表示させる(2)
- 複数の表からデータを取り出して表示させる(1)
- SQL関数を使って面倒な処理を簡単に済ませる
- SELECT文で取り出したデータを加工して表示する
- 複数の条件を指定してSELECT文を実行する
- まずはここから! 基本的なSELECT文から始めよう
- SQL実行環境を準備しよう
- SQLとはどういう言語か
Copyright © ITmedia, Inc. All Rights Reserved.