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)のケース
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 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 "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 select句で必要な列だけ取り出す (省略)
(2)のケース
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 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 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 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);