複数の表からデータを取り出して表示させる(2)Webブラウザで気軽に学ぶ実践SQL講座(8)

前回の連載では、SQLの基本的な結合構文について説明しました。結合構文を使うと、関係する複数の表から、関連するデータを取り出すことができます。今回は、より複雑な、自己結合、外部結合について説明します(編集部)

» 2012年04月23日 00時00分 公開

自己結合

連載バックナンバー


 自己結合とは、表をその表自体に結合する構文です。1つの表の中に互いに関係する列があり、その列同士の関係を使ってデータを取得したい場合に使用します。実際の例(図1)で考えてみましょう。

 今回使用しているEMP表には、社員の上司の情報を格納したマネージャ列「MGR」が存在します。MGR列を調べることで、社員の上司を検索することができます。

 例えば、社員「BLAKE」のMGR列には「7839」という値が格納されていますが、これは上司の社員番号(EMPNO)にあたります。つまり、社員「BLAKE」の上司は、社員番号7839の「KING」であることが分かります。このように、EMP表には、社員(部下)のMGR列には、上司のEMPNOが入っているという関係があります。

図1 部下「BLAKE」と上司「KING」の関係を示す表 図1 部下「BLAKE」と上司「KING」の関係を示す表

 このとき、部下「BLAKE」の名前と、上司「KING」の名前を表示するには、どのようなSQL文を記述すればよいでしょうか。

 次のように考えると、行き詰ってしまいますね。

 これは、部下の名前も、上司の名前も同じEMP表のename列に入っており、区別できないためです。

 そこで、前回紹介した表別名をうまく使い、部下用の表(eという別名で識別)と、上司用の表(mという別名で識別)を分けて考えればよいのです(図2)

図2 部下用と上司用の表を分けて考えた場合の表(クリックすると拡大) 図2 部下用と上司用の表を分けて考えた場合の表(クリックすると拡大)

 このようにすると、「e(部下用のEMP表)のMGR列には、m(上司用のEMP表)のEMONO列が入っている」という関係を表すことができます。

 これをもとにSQL文を書いてみましょう。

SELECT e.ename 部下名, m.ename 上司名
FROM   emp e JOIN emp m
ON     e.mgr=m.empno;
図3 2つの表を結合し、部下と上司の関係を表した 図3 2つの表を結合し、部下と上司の関係を表した

 少し複雑なので、もう一度整理して考えてみましょう。上記の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)。今回の連載では、これを「一致しないデータ」と呼びます。

図4 EMP表とDEPT表を比較(クリックすると拡大) 図4 EMP表とDEPT表を比較(クリックすると拡大)

 このような場合、今までの結合構文では、両方の表に一致するデータがある行のみが検索されるため、部門40は検索されません(図5)

SELECT e.ename, d.deptno, d.dname
FROM   emp e JOIN dept d
ON     e.deptno=d.deptno;
図5 内部結合では、両方の表に一致する行のみが表示される 図5 内部結合では、両方の表に一致する行のみが表示される

 一致しないデータ(この例では部門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;
図6 外部結合でEMP表に存在しない「部門40」も含めて結果データを表示 図6 外部結合でEMP表に存在しない「部門40」も含めて結果データを表示

 また、互いに一致するデータがない場合でも、両方の表すべてのデータを表示したい場合には、「FULL OUTER JOIN」を指定します。

コラム---Oracle独自の構文

 Oracle Databaseでは、上記で紹介したANSI規格準拠のほかに、Oracle独自の結合構文を使うこともできます。これは、Oracle9iリリース以前のデータベースでは、ANSI規格と異なる独自の構文を使っていたためですが、現在はどちらの構文も使うことができます。どちらの構文を使っても、できることは基本的には同じです。企業ごとののコーディング・ルールに合わせて、使用する構文を選択してください。

 ANSI準拠の標準構文とOracle独自の結合構文との比較については、次の表(図7)を参考にしてください。

図7 ANSI準拠構文とOracle独自の結合構文との比較表(クリックすると拡大) 図7 ANSI準拠構文とOracle独自の結合構文との比較表(クリックすると拡大)

 例えば、Oracle独自の結合構文で「社員名と社員の所属する部門名」を表示するには、以下のように指定します。

SELECT e.ename, d.deptno, d.dname
FROM   emp e, dept d
WHERE  e.deptno=d.deptno;
図8 Oracle独自の結合構文で社員名などを表示した例 図8 Oracle独自の結合構文で社員名などを表示した例

筆者紹介

日本オラクル オラクルダイレクト所属。

須々木尚子(すすき なおこ)

オンラインセミナーの講師や、お客様への提案、案件の支援などを担当。著書に「Oracle SQLクイズ」(翔泳社)があります。



Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

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

メールマガジン登録

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