HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

外部結合を侮ると痛い目見るの巻

SQL

まぁ、言うほど大げさな話でもないのだけど、少なくとも、結合条件と検索条件の違いが分かっていないと痛い目見るだろう、と。条件なんてON句に書いてもWHERE句に書いても一緒でしょ、と思っている人(誰)は要注意。

前提とするテーブルは次の2つ。(諸般の事情で、purchase_dateが文字列型になっているが、今回はそこには触れない)

CREATE TABLE product(
      product_code VARCHAR(16) NOT NULL
    , product_name VARCHAR(16) NOT NULL
)

CREATE TABLE purchase(
      purchase_date VARCHAR(10) NOT NULL
    , product_code VARCHAR(16) NOT NULL
    , purchase_count INT NOT NULL
)

それぞれデータは以下のような感じ。

  • product
product_code product_name
P01 Orange
P02 Apple
P03 Cherry
  • purchase
purchase_date product_code purchase_count
2011-03-02 P01 3
2011-03-18 P01 2
2011-04-02 P02 5
2011-04-11 P02 3
2011-03-02 P03 7
2011-03-18 P03 4
2011-04-02 P03 8
2011-04-11 P03 1

課題

2011年4月における、商品コード、商品名、各商品ごとの売り上げ個数の一覧を出力。

内部結合(INNER JOIN)

「2011年4月に購入がなかった商品は出力しなくてよい」ものとする。

この課題を解くには、次のようなSQL文が考えられる。(Oracleだけは、「AS」キーワードを取り除かないとエラーになる)

SELECT t1.product_code, t1.product_name, SUM(t2.purchase_count) AS cnt
 FROM product AS t1 INNER JOIN purchase AS t2
 ON t1.product_code = t2.product_code
 WHERE t2.purchase_date BETWEEN '2011-04-01' AND '2011-04-30'
 GROUP BY t1.product_code, t1.product_name

SELECT t1.product_code, t1.product_name, SUM(t2.purchase_count) AS cnt
 FROM product AS t1 INNER JOIN purchase AS t2
 ON t1.product_code = t2.product_code
   AND t2.purchase_date BETWEEN '2011-04-01' AND '2011-04-30'
 GROUP BY t1.product_code, t1.product_name

これらのSQL文を実行すると、いずれも次のような結果が返ってくる。

product_code product_name cnt
P02 Apple 8
P03 Cherry 9

もちろん、FROM句ではテーブル名をカンマで区切って並べ、すべての条件をWHERE句に書いてもうまくいくだろう。

外部結合(OUTER JOIN)

「2011年4月に購入がなかった商品も出力する」ものとする。

この課題を解くには、単純に内部結合の場合のSQL文の「INNER JOIN」を「LEFT JOIN」に置き換えればよさそう‥(Oracleだけは(以下略))

SELECT t1.product_code, t1.product_name, SUM(t2.purchase_count) AS cnt
 FROM product AS t1 LEFT JOIN purchase AS t2
 ON t1.product_code = t2.product_code
 WHERE t2.purchase_date BETWEEN '2011-04-01' AND '2011-04-30'
 GROUP BY t1.product_code, t1.product_name

SELECT t1.product_code, t1.product_name, SUM(t2.purchase_count) AS cnt
 FROM product AS t1 LEFT JOIN purchase AS t2
 ON t1.product_code = t2.product_code
   AND t2.purchase_date BETWEEN '2011-04-01' AND '2011-04-30'
 GROUP BY t1.product_code, t1.product_name

‥なわけはない。この2者には決定的な違いがある。
実際、これらを実行してみると、それぞれ次のような結果が得られる。

product_code product_name cnt
P02 Apple 8
P03 Cherry 9
product_code product_name cnt
P01 Orange 0
P02 Apple 8
P03 Cherry 9


前者のSQL文の場合、次のような手順で結果が導き出される。
1. 「t1.product_code = t2.product_code」の評価(ON句)

t1.product_code t1.product_name t2.purchase_date t2.product_code t2.purchase_count
P01 Orange 2011-03-02 P01 3
P01 Orange 2011-03-18 P01 2
P02 Apple 2011-04-02 P02 5
P02 Apple 2011-04-11 P02 3
P03 Cherry 2011-03-02 P03 7
P03 Cherry 2011-03-18 P03 4
P03 Cherry 2011-04-02 P03 8
P03 Cherry 2011-04-11 P03 1

2. 「t2.purchase_date BETWEEN '2011-04-01' AND '2011-04-30'」で絞込み(WHERE句)

t1.product_code t1.product_name t2.purchase_date t2.product_code t2.purchase_count
P02 Apple 2011-04-02 P02 5
P02 Apple 2011-04-11 P02 3
P03 Cherry 2011-04-02 P03 8
P03 Cherry 2011-04-11 P03 1

3. 「t1.product_code, t1.product_name」(GROUP BY句)と「t1.product_code, t1.product_name, SUM(t2.purchase_count) AS cnt」(SELECT句)

t1.product_code t1.product_name cnt
P02 Apple 8
P03 Cherry 9


後者のSQL文の場合、次のような手順で結果が導き出される。
1. 「t2.purchase_date BETWEEN '2011-04-01' AND '2011-04-30'」の評価(ON句)

  • purchase
t1.purchase_date t1.product_code t2.purchase_count
2011-04-02 P02 5
2011-04-11 P02 3
2011-04-02 P03 8
2011-04-11 P03 1

2. 「t1.product_code = t2.product_code」の評価(ON句)

t1.product_code t1.product_name t2.purchase_date t2.product_code t2.purchase_count
P01 Orange - - -
P02 Apple 2011-04-02 P02 5
P02 Apple 2011-04-11 P02 3
P03 Cherry 2011-04-02 P03 8
P03 Cherry 2011-04-11 P03 1

3. 「t1.product_code, t1.product_name」(GROUP BY句)と「t1.product_code, t1.product_name, SUM(t2.purchase_count) AS cnt」(SELECT句)

t1.product_code t1.product_name cnt
P01 Orange 0
P02 Apple 8
P03 Cherry 9


つまり、前者の場合、先にproduct_code列での結合が行われるため、productテーブル(t1)とpurchaseテーブル(t2)には、共にproduct_code "P01"が存在するため、通常の内部結合と同じ結果になる。その上で日付で絞り込むので、"P01"を含む行は除外される。
一方、後者の場合、先にpurchase_date列での絞込みをした後でproduct_code列での結合が行われる*1ため、絞り込んだpurchaseテーブル(t2)には"P01"を含む行が存在しない状態で結合することになる。そのため、productテーブル(t1)中のproduct_code "P01"に対して、各値がNULLの行が結合される。


ついでに言うと、後者のケースで、さらにWHERE句で絞り込む場合、purchaseテーブル(t2)側の列の値はNULLになっている可能性があることを忘れてはいけない。

試したDBMS

本題ではなかったので最後に書くが、上記のSQL文を実際に実行してみたRDBMSは以下のとおり。(以下は、DatabaseMetaDataのgetDatabaseProductName()とgetDatabaseProductVersion()を適当に組み合わせた文字列)

*1:この辺、実はちょっと自信がない。ON句に書いた単一テーブルに対する条件は、複数テーブルにまたがる条件よりも先に評価されるという規則があるのだろうか‥