SQLって深いよね、ほんと。 Tweet

先日、以下のようなクエリを見てこんな書き方見た事無いとか、クエリが間違ってんじゃないのとか騒いでいたので、そんなこと無いよ！　と一言。（アプリケーションの要求仕様までは把握していないので、アプリケーション的に正しいのか？ってところまでは不明だったのですが…）
外部結合でOracleの方言を使っちゃうとパッと見、結合条件なのか選択条件なのか分かり憎いのは間違いないので、利用できるリリースのOracleなら標準的な記述にしたほうがいいよねというお話。

以下、Oracleの方言で外部結合を行うクエリ。５行目の部分が騒ぎの発端。多分、みなさん結合条件じゃなくて、選択条件と勘違いしたか、結合条件だと判断してたとしても何か勘違いした模様で騒いでいたらしい。

SCOTT> l

  1  SELECT ename,dname,loc
  2  FROM emp,dept
  3  WHERE
  4    emp.deptno = dept.deptno(+)
  5*   AND dept.loc(+) = 'NEW YORK'
SCOTT> /

ENAME      DNAME          LOC
---------- -------------- -------------
MILLER     ACCOUNTING     NEW YORK
KING       ACCOUNTING     NEW YORK
CLARK      ACCOUNTING     NEW YORK
scott
JAMES
TURNER
BLAKE
MARTIN
WARD
ALLEN
FORD
JONES
SMITH
ほげ

14行が選択されました。

SCOTT>

前述のようなOracleの方言は標準的な記述に書き換えると分り易くなるよ！

どうですか？　外部結合の結合条件だと一発で分りますよね！

SCOTT> l

  1  SELECT ename,dname,loc
  2  FROM
  3    emp LEFT OUTER JOIN dept
  4    ON emp.deptno = dept.deptno
  5*   AND dept.loc = 'NEW YORK'
SCOTT> /

ENAME      DNAME          LOC
---------- -------------- -------------
MILLER     ACCOUNTING     NEW YORK
KING       ACCOUNTING     NEW YORK
CLARK      ACCOUNTING     NEW YORK
scott
JAMES
TURNER
BLAKE
MARTIN
WARD
ALLEN
FORD
JONES
SMITH
ほげ

14行が選択されました。

SCOTT>

え、まだ、もやもやしてるって？！

じゃあ、副問合せで書き換えてみたらこのクエリの意味が分り易いんじゃないかな？　以下の例のように…

SCOTT> l

  1  SELECT ename,dname,loc
  2  FROM
  3    emp LEFT OUTER JOIN
  4    (
  5       SELECT *
  6       FROM dept
  7       WHERE loc = 'NEW YORK'
  8    ) d
  9*   ON emp.deptno = d.deptno
SCOTT> /

ENAME      DNAME          LOC
---------- -------------- -------------
MILLER     ACCOUNTING     NEW YORK
KING       ACCOUNTING     NEW YORK
CLARK      ACCOUNTING     NEW YORK
scott
JAMES
TURNER
BLAKE
MARTIN
WARD
ALLEN
FORD
JONES
SMITH
ほげ

14行が選択されました。

SCOTT>

前述のクエリをOracleの方言で外部結合すると…

SCOTT> l

  1  SELECT ename,dname,loc
  2  FROM
  3    emp
  4    ,(
  5       SELECT *
  6       FROM dept
  7       WHERE loc = 'NEW YORK'
  8     ) d
  9  WHERE
 10*   emp.deptno = d.deptno(+)
SCOTT> /

ENAME      DNAME          LOC
---------- -------------- -------------
MILLER     ACCOUNTING     NEW YORK
KING       ACCOUNTING     NEW YORK
CLARK      ACCOUNTING     NEW YORK
scott
JAMES
TURNER
BLAKE
MARTIN
WARD
ALLEN
FORD
JONES
SMITH
ほげ

14行が選択されました。

SCOTT>

どう？　わかった？