技術memo

関数型ゴースト

SQLのouter joinの罠にハマった話

結論

join on ほにゃららと、where ほにゃららだと、outer joinのときに挙動が違います。 outer joinのouter側のテーブルへの条件は、onのところに書きましょう。 left outer joinでwhereのところにright側テーブルに関する条件を書いた場合、条件に合致しなかったleft側の行が出力されなくなってしまいます。

当たり前といえば当たり前なんですが、ハマると酷いので覚え書きします。 環境はたぶんどのDBMSでも変わりませんが、動作確認はSQL Server Expressです。

テーブル構成

以下のようなテーブルがあるとします。 若干例がひどい気もしますが、気にしないでください。

COMPANY(企業)

COMPANY_ID NAME
0 comA
1 comB
2 comC

PRODUCTS(製品)

PRODUCT_ID COMPANY_ID NAME YEAR
0 0 M 1998
1 0 N 2000
2 1 O 2000
3 1 P 2000

PRODUCTSテーブルには企業AとBの製品が2つずつ登録されています。企業Cのものはありません。

クエリ

このとき、以下のようなSQLを実行するとします。

(1)

    select
        COMPANY.NAME as C_NAME
        , PRODUCTS.NAME as P_NAME
        , PRODUCTS.YEAR as P_YEAR
    from
        COMPANY
    left outer join
        PRODUCTS
    on
        COMPANY.COMPANY_ID = PRODUCTS.COMPANY_ID
    where
        PRODUCTS.YEAR = 2000

(2)

    select
        COMPANY.NAME as C_NAME
        , PRODUCTS.NAME as P_NAME
        , PRODUCTS.YEAR as P_YEAR
    from
        COMPANY
    left outer join
        PRODUCTS
    on
        COMPANY.COMPANY_ID = PRODUCTS.COMPANY_ID
        and PRODUCTS.YEAR = 2000

どちらも、COMPANYとPRODUCTSテーブルから、PRODUCTS.YEARが2000の要素を取り出そうしています。 left outer joinなのは、企業に製品が無いときはそのまま取得したいからです。

結果

(1)

C_NAME P_NAME P_YEAR
comA N 2000
comB O 2000
comB P 2000

(2)

C_NAME P_NAME P_YEAR
comA N 2000
comB O 2000
comB P 2000
comC NULL NULL

解説

(1)のケースだと、対応するレコードの無かった企業Cの行(comC, NULL, NULL)が無かったことにされています。 これではleft outer joinなのにinner joinと変わりません。

何故でしょうか。 それは、式"PRODUCTS.YEAR = 2000"が評価されるタイミングを考えれば見えてきます。

(1)のケース

  1. COMPANYテーブルとPRODUCTSテーブルを無条件で総当り的にjoinする

    COMPANY.COMPANY_ID COMPANY.NAME PRODUCTS.PRODUCT_ID PRODUCTS.COMPANY_ID PRODUCTS.NAME PRODUCTS.YEAR
    0 comA 0 0 M 1998
    0 comA 1 0 N 2000
    0 comA 2 1 O 2000
    0 comA 3 1 P 2000
    1 comB 0 0 M 1998
    1 comB 1 0 N 2000
    1 comB 2 1 O 2000
    1 comB 3 1 P 2000
    2 comC 0 0 M 1998
    2 comC 1 0 N 2000
    2 comC 2 1 O 2000
    2 comC 3 1 P 2000
  2. COMPANY_ID列同士の一致を確認する ※この段階でleft outer joinの処理が完了する

    COMPANY.COMPANY_ID COMPANY.NAME PRODUCTS.PRODUCT_ID PRODUCTS.COMPANY_ID PRODUCTS.NAME PRODUCTS.YEAR
    0 comA 0 0 M 1998
    0 comA 1 0 N 2000
    1 comB 2 1 O 2000
    1 comB 3 1 P 2000
    2 comC NULL NULL NULL NULL
  3. "PRODUCTS.YEAR = 2000"の条件で絞り込む

    COMPANY.COMPANY_ID COMPANY.NAME PRODUCTS.PRODUCT_ID PRODUCTS.COMPANY_ID PRODUCTS.NAME PRODUCTS.YEAR
    0 comA 1 0 N 2000
    1 comB 2 1 O 2000
    1 comB 3 1 P 2000
  4. select句で必要な列だけ取り出す (省略)

(2)のケース

  1. COMPANYテーブルとPRODUCTSテーブルを無条件で総当り的にjoinする

    COMPANY.COMPANY_ID COMPANY.NAME PRODUCTS.PRODUCT_ID PRODUCTS.COMPANY_ID PRODUCTS.NAME PRODUCTS.YEAR
    0 comA 0 0 M 1998
    0 comA 1 0 N 2000
    0 comA 2 1 O 2000
    0 comA 3 1 P 2000
    1 comB 0 0 M 1998
    1 comB 1 0 N 2000
    1 comB 2 1 O 2000
    1 comB 3 1 P 2000
    2 comC 0 0 M 1998
    2 comC 1 0 N 2000
    2 comC 2 1 O 2000
    2 comC 3 1 P 2000
  2. COMPANY_ID列同士の一致と、"PRODUCTS.YEAR = 2000"の条件への一致を確認する

    COMPANY.COMPANY_ID COMPANY.NAME PRODUCTS.PRODUCT_ID PRODUCTS.COMPANY_ID PRODUCTS.NAME PRODUCTS.YEAR
    0 comA 1 0 N 2000
    1 comB 2 1 O 2000
    1 comB 3 1 P 2000
  3. left outer joinなので、PRODUCTSテーブルに対応するデータの無かったCOMPANYテーブルの行を復活させる。PRODUCTSテーブル側の列はNULLとする。

    COMPANY.COMPANY_ID COMPANY.NAME PRODUCTS.PRODUCT_ID PRODUCTS.COMPANY_ID PRODUCTS.NAME PRODUCTS.YEAR
    0 comA 1 0 N 2000
    1 comB 2 1 O 2000
    1 comB 3 1 P 2000
    2 comC NULL NULL NULL NULL
  4. select句で必要な列だけ取り出す (省略)

以上、あまりにも単純な落とし穴でした。

この問題、そもそもこんなクエリを書くべきではないような気がするのですが、実際outer joinの使いどころとしてはどうなんでしょうか。 例えば(left outer joinの)right側のテーブルが何らかの名前のマスタで、論理削除フラグを持つような場合などは、こういうケースになりそうな気がします。 ですが、そもそもそういうパターンなら一旦SQL発行元(アプリケーション)でデータをキャッシュして、メモリ上でjoinする方がベターにも見えますし。DB側との性能差まで考えるとどちらとも言えなさそうですね。

例を動かす準備のためのSQL

    create table COMPANY(
        COMPANY_ID int
        , NAME nchar(10)
    );
    insert into COMPANY(COMPANY_ID, NAME) VALUES(0, 'comA');
    insert into COMPANY(COMPANY_ID, NAME) VALUES(1, 'comB');
    insert into COMPANY(COMPANY_ID, NAME) VALUES(2, 'comC');
    create table PRODUCTS(
        PRODUCT_ID int
        , COMPANY_ID int
        , NAME nchar(10)
        , YEAR int
    );
    insert into PRODUCTS(PRODUCT_ID, COMPANY_ID, NAME, YEAR) VALUES(0, 0, 'M', 1998);
    insert into PRODUCTS(PRODUCT_ID, COMPANY_ID, NAME, YEAR) VALUES(1, 0, 'N', 2000);
    insert into PRODUCTS(PRODUCT_ID, COMPANY_ID, NAME, YEAR) VALUES(2, 1, 'O', 2000);
    insert into PRODUCTS(PRODUCT_ID, COMPANY_ID, NAME, YEAR) VALUES(3, 1, 'P', 2000);