WHERE句で絞り込んでから結合する方法

多対多の関係を持つ下記のER図を例に説明します。

課題

Itemsテーブルの全レコードを列挙して、ある特定のCustomerの「買った・買っていない」を表示したい場合、SQLでどんなクエリを発行するのか悩みました。

失敗作

単に結合すると、全てのCustomerの購入履歴が表示されてしまいます。

sqlite> SELECT * FROM Items LEFT OUTER JOIN 
    Orders ON Items.id = Orders.item_id; 

テーブルを結合した後にWHERE句で絞り込むと、指定したCustomerが購入したItemは表示されますが、購入していないItemは表示されません。

sqlite> SELECT * FROM Items LEFT OUTER JOIN 
    Orders ON Items.id = Orders.item_id WHERE Orders.customer_id = 1;

解決策

サブクエリ（副問い合わせ）を使うことで解決できました。
サブクエリでOrdersをWHERE句で、特定のCustomerに絞り込んでから結合します。

sqlite> SELECT * FROM Items LEFT OUTER JOIN 
    (SELECT * FROM Orders WHERE customer_id = 1) o ON Items.id = o.item_id;

まとめ

ActiveRecordのようなORMを使うと簡単に扱えるhas-manyの関係ですが、SQLで扱うのは少し難しいです。