基本に戻ろう: 外部結合
参照元:Back to basics: outer joins < Eddie Awad’s Blog
— beginning of translation —
この記事はOracle結合シリーズの一つです。
目次：
1.基本に戻ろう: クロス結合
2.基本に戻ろう: 内部結合
3.基本に戻ろう: 外部結合
4.基本に戻ろう: 等価結合と非等価結合
5.基本に戻ろう: 自己結合
6.基本に戻ろう: アンチジョインとセミジョイン
結合シリーズを続けます。今日は外部結合について解説します。外部結合は内部結合の結果を拡張したものです。外部結合は結合される両方の表が結合条件を満たす行も、一方の表には条件を満たす行がない行もすべて返します。
ANSI SQL構文に準拠する外部結合:
FROM t1 { LEFT | RIGHT | FULL } [OUTER] JOIN t2
キーワードOUTERは任意です。LEFT、RIGHT、FULLが記述されている場合は外部結合とみなされます。
左外部結合
「LEFT」を記述することにより、t1のすべての行を含む結果セットが生成されます。t1には存在し、t2には存在しない行の列にはNULL値がセットされます。
例えば、従業員が所属しているかどうかにかかわらず、すべての部署をリストする場合、dept表とemp表を左外部結合で結合します:
SELECT dept.dept_name, emp.emp_name FROM dept LEFT OUTER JOIN emp ON dept.dept_id = emp.dept_id DEPT_NAME EMP_NAME ---------- --------- HR IT Eddie 2 rows selected
上記を従来の構文で記述すると:
SELECT dept.dept_name, emp.emp_name FROM dept, emp WHERE dept.dept_id = emp.dept_id(+)
右外部結合
「emp.dept_id」の後ろの(+)演算子は、emp表に一致する行が存在しないdept表の行も表示するということを意味します。
「RIGHT」を記述することにより、t2のすべての行を含む結果セットが生成されます。t2には存在し、t1には存在しない行の列にはNULL値がセットされます。
例えば、部署に所属しているかどうかにかかわらず、すべての従業員をリストする場合、emp表とdept表を右外部結合で結合します:
SELECT dept.dept_name, emp.emp_name
FROM dept RIGHT OUTER JOIN emp
ON dept.dept_id = emp.dept_id
DEPT_NAME EMP_NAME
---------- ---------
King
IT Eddie
2 rows selected
従来の構文で記述すると:
SELECT dept.dept_name, emp.emp_name FROM dept, emp WHERE dept.dept_id(+) = emp.dept_id
「dept.dept_id」の後ろの(+)演算子は、dept表に一致する行が存在しないemp表の行も表示するということを意味します。
左結合か右結合か？
外部結合でキーワード「LEFT」と「RIGHT」のどちらを使うかはFROM句で記述する表の位置で決まります: FROM句のt1とt2を見てください。もし左側にt1が記述されていて、t1の全ての行を抽出したいのであれば左結合を使うことになりますし、右側にt1が記述されていて、t1の全ての行を抽出するのであれば右結合を使うことになります。
全外部結合
「FULL」を記述することにより、t1、t2共にすべての行を含む結果セットが生成されます。t1には存在しt2には存在しない行は、結果セットのt2の列にNULL値がセットされます。また、t2には存在しt1には存在しない行は、結果セットのt1の列にNULL値がセットされます。
例えば、すべての部署(所属する従業員がいるいないに関わらず)を表示しながら、すべての従業員(特定の部署に所属しているいないに関わらず)も表示するには全外部結合を使います。
SELECT dept.dept_name, emp.emp_name
FROM dept FULL OUTER JOIN emp
ON dept.dept_id = emp.dept_id
DEPT_NAME EMP_NAME
---------- ---------
HR
IT Eddie
King
3 rows selected
従来の構文で記述すると:
SELECT dept.dept_name, emp.emp_name FROM dept, emp WHERE dept.dept_id(+) = emp.dept_id(+)
おっと！上記の文を実行すると次のようなエラーになります。
ORA-01468: 1 つの述語に対して外部結合表は1つのみ参照できます
全外部結合は従来の構文ではサポートされていないためこのようなことが起こります。外部結合演算子(+)は結合条件の一方にしか使用できないのです。(回避策としては２つのSELECT文をUNIONで結合する方法があります。)
ANSI SQLを使うメリットがもう一つあります。(+)演算子を含む条件はIN演算子と一緒に使えなかったり、OR演算子を使った他の条件と繋げて記述することができないのですが、ANSI SQLを使うことによりそのような制限を取り除くことができます。例えば:
SELECT dept.dept_name, emp.emp_name FROM dept, emp WHERE dept.dept_id(+) = emp.dept_id OR emp.emp_name = 'King'
これは次のようなエラーになります:
ORA-01719: ORまたはINオペランドの中で外部結合演算子(+)は使用できません
ANSI SQL結合構文を使ってORA-01719エラーが発生しないようにするには次のように記述します:
SELECT dept.dept_name, emp.emp_name
FROM dept RIGHT OUTER JOIN emp
ON (dept.dept_id = emp.dept_id
OR emp.emp_name = 'King')
DEPT_NAME EMP_NAME
---------- ---------
HR King
IT King
IT Eddie
3 rows selected
これで外部結合についての解説は終わりです。残りの結合タイプについては後日説明します…
— end of translation —