対象レコードを絞ってOUTER JOINしたいんです

oracle db

先に書くと、絞ってからjoinしたいならonのところで絞っておくこと。特にouter joinは。
inner joinはどっちでもいいけど、可読性考えて同じようにonに書くのがいいでしょうね。

職種一覧と、各職種で最も最近雇用した人を表示するSQLを書こうとします。同時雇用した人がいたらしょうがないってことで。。

職種はjobsにあり、従業員リストと雇用日の情報はemployeesが持っています。
リレーションはこちらを参照してください。。

SELECT
  J.JOB_ID
  ,E.FIRST_NAME || ' ' || E.LAST_NAME AS NAME
  ,HIRE_DATE
FROM
  JOBS J
    LEFT OUTER JOIN (
      SELECT
        EMP.*
        ,MAX(HIRE_DATE) OVER (PARTITION BY JOB_ID) AS LAST_DATE
      FROM
        EMPLOYEES EMP
    ) E
      ON J.JOB_ID = E.JOB_ID
      AND E.HIRE_DATE = E.LAST_DATE
;

こう書いてみました。
「employees各行に対しウィンドウ関数で職種ごとのmax雇用日を追加するをサブクエリ」をouter joinしていて、ONに条件を追加することで外部結合時にレコードを絞れます。

これをwhereで絞ってしまうと、外部結合ですから結合されたカラムがnullになるような行が出てくるわけで、その行が除外されてしまいます。
この例だとたまたま（たまたまじゃないか。。）全職種の人がemployeesにいるので以下のように出力されます。

JOB_ID        NAME                HIRE_DAT
------------- ------------------- --------
AC_ACCOUNT    William Gietz       02-06-07
AC_MGR        Shelley Higgins     02-06-07
AD_ASST       Jennifer Whalen     03-09-17
AD_PRES       Steven King         03-06-17
AD_VP         Neena Kochhar       05-09-21
FI_ACCOUNT    Luis Popp           07-12-07
FI_MGR        Nancy Greenberg     02-08-17
HR_REP        Susan Mavris        02-06-07
IT_PROG       Bruce Ernst         07-05-21
MK_MAN        Michael Hartstein   04-02-17
MK_REP        Pat Fay             05-08-17
PR_REP        Hermann Baer        02-06-07
PU_CLERK      Karen Colmenares    07-08-10
PU_MAN        Den Raphaely        02-12-07
SA_MAN        Eleni Zlotkey       08-01-29
SA_REP        Amit Banda          08-04-21
SA_REP        Sundita Kumar       08-04-21
SH_CLERK       yamada             13-08-03
ST_CLERK      Steven Markle       08-03-08
ST_MAN        Kevin Mourgos       07-11-16

20行が選択されました。

でも現在社内に人がいない職種があったら

JOB_ID        NAME                HIRE_DAT
------------- ------------------- --------
AC_ACCOUNT    William Gietz       02-06-07
AC_MGR
AD_ASST
AD_PRES       Steven King         03-06-17

こんな感じになります。
このようにouter joinされたあとにE.HIRE_DATE = E.LAST_DATEなんてしたらAC_MGR, AD_ASSTの行が選択されなくなってしまう、とこういうわけですね。

inner joinだとマッチしない行はそもそも選択されないので、絞り込み条件をonに書こうがwhereに書こうが同じ。ですよね。。

標準SQLで複雑なJOINを書く時にいつも微妙に困っていたのでちょっとすっきり。

参考

結合する表がたくさんある場合はjoinをどんどん繋げていって、onにはそこまでに登場した表のカラムを使っていいんだよってことですね。

本件ではこの2ページが一番参考になりました。

上記を読む前に「そもそも構文どうなってんだよ。。」と思って調べたのが以下。Syntax Rulesの所は全然読んでません。

結局はこんな感じ：
INNER | {LEFT | RIGHT | FULL}[ OUTER] JOIN table[ PARTITION BY (column[, column...])] {ON search_condition | USING (column[, column...])}
onの所はもうなんでもありって感じ？
で、「え、joinにpartition byなんて書けるの？」って分かって読んだのが以下。

使いどころが来た時にこの機能を思い出せるかどうか自信ないです。。