表の結合

oracle

結合とは、2つ以上の表の情報を合わせて取り出す問い合わせのこと。
Oracleでは独自構文で表結合していたが、Oracle9iからSQL:1999構文も使用できる。

(環境)
SQL> DESC EMP
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 EMPNO					   NOT NULL NUMBER(4)
 EMPNAME					    VARCHAR2(20)
 SAL						    NUMBER(10)
 DEPTNO 					    NUMBER(4)

SQL> DESC DEPT
 名前                                    NULL?    型
 ----------------------------------------- -------- ----------------------------
 DEPTNO 				   NOT NULL NUMBER(4)
 DEPTNAME					    VARCHAR2(10)

等価結合(自然結合)

(Oracle独自)

  • 結合列をWHERE句で条件指定する
  • 等価結合は、互いの表の値が等しい結合
  • 結合列は、表名で修飾する
SELECT DEPT.DEPTNO, DEPT.DEPTNAME, EMP.EMPNO, EMP.EMPNAME FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO;

(SQL:1999)

  • NATURAL JOIN句で結合する
  • 両方の表で、同じ列名及び同じデータ型の列を結合列とする(結合列の明示的指定は不要)
  • 上記条件が合致する列が複数ある場合は、全て結合に使用される
  • 結合列を明示的に指定する場合やデータ型が異なる列を結合列にする場合は、USING句を使用する
  • NATURAL JOIN句とUSING句は排他的
  • 結合列は、表名または別名で修飾できない(結合列以外は、修飾可能)
SELECT DEPTNO, DEPTNAME, EMPNO, EMPNAME FROM DEPT NATURAL JOIN EMP;
SELECT DEPTNO, DEPTNAME, EMPNO, EMPNAME FROM DEPT JOIN EMP USING (DEPTNO);

直積結合(クロス結合)

表内の全ての行を互いの表の全ての行に結合する。(出力数は全行数*全行数)
(Oracle独自)

  • FROM句に2つ以上の表名を指定
  • 結合条件がない
  • 結合条件が不適切
  • 両方の表に存在する列は、表名を修飾する必要がある
SELECT DEPT.DEPTNO, DEPTNAME, EMPNO, EMPNAME FROM DEPT, EMP;

(SQL:1999)

  • CROSS JOIN句を使用する
  • 両方の表に存在する列は、表名を修飾する必要がある
SELECT DEPT.DEPTNO, DEPTNAME, EMPNO, EMPNAME FROM DEPT CROSS JOIN EMP;

非等価結合(ON句結合)

(Oracle独自)

  • WHERE句に非等価な条件(BETWEEN〜AND〜等)を指定する
SELECT DEPT.DEPTNO, DEPTNAME, EMPNO, EMPNAME FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND SAL BETWEEN 5000 AND 10000;

(SQL:1999)

  • JOIN〜ON句で結合列を指定し、WHERE句で非等価条件を指定する
SELECT DEPT.DEPTNO, DEPTNAME, EMPNO, EMPNAME FROM DEPT JOIN EMP ON DEPT.DEPTNO = EMP.DEPTNO WHERE SAL BETWEEN 5000 AND 10000;

自己結合

表別名を使用し、1つの表を結合する
(Oracle独自)

  • FROM句で表別名を指定し、WHERE句で結合列(別名修飾)を指定する
  • SQL文では、必ず表別名を使用する

(SQL:1999)

  • FROM句で表別名を指定し、JOIN〜ON句で結合列(別名修飾)を指定する
  • SQL文では、必ず表別名を使用する

外部結合

他方の表の行とは一致しない行を含め、結合で戻された全ての行を返す
(Oracle独自)

  • 外部結合演算子(+)を使用する
  • 外部結合演算子は、他方の表と一致する行を持っていない方に付与する
SELECT DEPT.DEPTNO, DEPT.DEPTNAME, EMP.EMPNO, EMP.EMPNAME FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO(+);

(SQL:1999)

  • 左側外部結合(LEFT OUTER JOIN) ->FROM句で指定された表の持つ一致しない行も含める
  • 右側外部結合(RIGHT OUTER JOIN) ->JOIN句で指定された表の持つ一致しない行も含める
  • 完全外部結合(FULL OUTER JOIN) ->両方の表にある一致しない行を含める