SQLのJOINの種類:INNER、LEFT、RIGHT、FULL
SQLのJOIN(テーブル結合)は、条件が一致するところで2つのテーブルの行を横に並べます。データベースはわざとデータを分けて持っています。ordersにはcustomer_idだけがあり、顧客の名前はcustomersに1回だけ入っています。JOINの種類で決まるのは、相手のいない行をどう扱うかです。内部結合(INNER JOIN)は一致した行だけを残し、外部結合(LEFT JOINなど)は相手のいない行も残します。
2つの小さなテーブル
| customer_id | first_name |
|---|---|
| 1 | Ana |
| 2 | Ben |
| 3 | Chloe |
| order_id | customer_id | total |
|---|---|---|
| 101 | 1 | 40.00 |
| 102 | 1 | 15.50 |
| 103 | 2 | 22.00 |
| 104 | 9 | 60.00 |
Anaの注文は2件、Benは1件です。Chloeには注文がなく、注文104はcustomersにいない顧客9を指しています。
INNER JOIN:一致する行だけ
SELECT o.order_id, c.first_name, o.total
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id;
結果は3行です。Anaの101と102、Benの103です。Chloeには注文がなく、注文104には顧客がないので、どちらも消えます。JOINだけで書くとINNER JOINの意味になります。oとcは別名(エイリアス)で、列がどのテーブルのものかを示す短い名前です。
LEFT JOIN:最初のテーブルの行をすべて残す
SELECT c.first_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id;
結果は4行です。Anaが2回、Benが1回、そしてorder_idがNULLのChloeです。WHERE o.order_id IS NULLを足すと、一度も注文していない顧客が取り出せます。SQLのテストでよく出る問題です。先に書いたテーブルの行がすべて残るので、customers LEFT JOIN ordersとorders LEFT JOIN customersは違う問いに答えます。
RIGHT JOINとFULL OUTER JOIN
RIGHT JOINは、2つ目のテーブルの行をすべて残します。多くの人はテーブルを入れ替えてLEFT JOINで書き、どのクエリも同じ向きで読めるようにしています。FULL OUTER JOINは両側の一致しない行を残します。上のテーブルでは、注文のないChloeと顧客のない注文104を含む5行になります。2つの一覧を比べて、どこにもつながらない行を探すのに便利です。MySQLにはFULL OUTER JOINがないので、LEFT JOINとRIGHT JOINをUNIONで重ねます。
自己結合とCROSS JOIN
自己結合は、1つのテーブルに2つの別名を付けて、自分自身と結合します。たとえば、同じemployeesテーブルで社員と上司を結びつけます。CROSS JOINはすべての行をすべての行と組み合わせるので、顧客3人と注文4件なら12行です。すべての日付とすべての商品の組み合わせが必要なときには役立ちますが、それ以外ではほとんどが間違いです。
行数が変わってしまう間違い
- 違う列で結合しても、エラーにならずにおかしな結果が返ります。
ON o.order_id = c.customer_idも正しいSQLです。両方の列が同じものを表しているか確かめてください。 - 片側でキーが重複していると、行が増えます。
customersに顧客1の行が2つあれば、Anaの注文はそれぞれ2回返り、合計も2倍になります。結合の前後で行数を数えましょう。 - 2つ目のテーブルの条件を
WHEREに書くと、LEFT JOINの効果が消えます。WHERE o.total > 20はo.totalがNULLの行を捨てるので、注文のない顧客がまた消えます。残したいなら、その条件はON句に書きます。 - 両方のテーブルにある列名には、テーブルを付けます。
o.customer_idかc.customer_idと書かないと、列があいまいだというエラーで止まります。
このレッスンの練習問題では、架空のオンラインストアKestrelの注文と顧客でINNER JOINを練習します。答えは返ってくる行で確認するので、正しいクエリであればどの書き方でも正解になります。