SQL入門 STEP13｜LEFT JOINを使ってみよう

karin@taishi@felmia

INNER JOINでは表示されないデータがある

STEP12では、INNER JOINを使ってproductsとcategoriesを結合しました。

使ったのは、次のようなSQLです。

SELECT
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
INNER JOIN categories AS c
    ON p.category_id = c.category_id;

products.category_idとcategories.category_idが一致するデータを結び付けることで、

ノートPC    | 120000 | PC
マウス      | 3000   | 周辺機器
USBメモリ   | 2000   | アクセサリ

のように、商品とカテゴリー名を一緒に取得できました。

では、ここで新しい商品を1つ追加してみます。

カテゴリーが決まっていない商品を追加しよう

新しく「タブレット」という商品を登録するとします。

ただし、まだどのカテゴリーにするか決まっていません。

そんな商品を、category_idを指定せずに登録してみましょう。

INSERT INTO products (product_id, product_name, price)
VALUES (9, 'タブレット', 50000);

登録できたら確認します。

SELECT *
FROM products
WHERE product_id = 9;

結果を見ると、category_idには値が入っていません。

product_id | product_name | price | category_id
-----------+--------------+-------+------------
9          | タブレット     | 50000 | NULL

このNULLは、値が入っていない状態を表しています。

STEP9のCOUNTで少しだけ登場しましたね。

今回はNULLそのものを詳しく学ぶのが目的ではないので、

「タブレットには、まだカテゴリーが設定されていない」

と考えれば大丈夫です。

ルミィ

商品は登録されているけど、どのカテゴリーなのかはまだ決まっていないんだね。

INNER JOINで取得してみよう

この状態で、STEP12と同じINNER JOINを実行します。

SELECT
    p.product_id,
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
INNER JOIN categories AS c
    ON p.category_id = c.category_id
ORDER BY p.product_id ASC;

結果を確認すると、商品ID1～8は表示されます。

ところが、先ほど追加したタブレットが表示されません。

なぜでしょうか。

タブレットのcategory_idはNULLです。

一方、categoriesには、

category_id | category_name
------------+---------------
1           | PC
2           | 周辺機器
3           | アクセサリ

しかありません。

タブレットには、結び付けられるカテゴリーがない状態です。

INNER JOINは、ONの条件に一致するデータを取得するJOINでした。

そのため、結合相手が見つからないタブレットは結果に表示されません。

INNER JOINの場合

結合条件に一致するデータだけが結果に表示されます。

今回のタブレットにはcategory_idが設定されていないため、categories側に結合できるデータがありません。

そのため、INNER JOINの結果には表示されません。

商品自体は残して表示したい

ここで考えてみましょう。

タブレットにはカテゴリーが設定されていません。

だからといって、

「商品一覧からタブレット自体が消えてほしい」

とは限りませんよね。

例えば商品管理画面なら、

ノートPC     | PC
マウス       | 周辺機器
USBメモリ    | アクセサリ
タブレット   | NULL

のように、カテゴリーが決まっていない商品も含めて一覧にしたいことがあります。

そんなときに使えるのがLEFT JOINです。

LEFT JOINを使ってみよう

先ほどのINNER JOINをLEFT JOINに変えてみます。

SELECT
    p.product_id,
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
ORDER BY p.product_id ASC;

実行すると、今度は商品ID9のタブレットも表示されます。

product_id | product_name    | price  | category_name
-----------+-----------------+--------+---------------
1          | ノートPC         | 120000 | PC
2          | マウス           | 3000   | 周辺機器
3          | キーボード        | 8000   | 周辺機器
4          | モニター          | 30000  | 周辺機器
5          | USBメモリ        | 2000   | アクセサリ
6          | Webカメラ        | 7000   | 周辺機器
7          | ヘッドセット      | 7000   | 周辺機器
8          | HDMIケーブル 2m   | 1800   | アクセサリ
9          | タブレット        | 50000  | NULL

タブレットに対応するカテゴリーはありません。

そのためcategory_nameはNULLですが、商品そのものは結果に残っています。

ここがINNER JOINとの大きな違いです。

ルミィ

LEFT JOINなら、カテゴリーが決まっていないタブレットも表示されたね！

LEFT JOINは左側のテーブルを残す

では、なぜ「LEFT」という名前なのでしょうか。

今回のSQLの一部を見てみます。

FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id

この場合、

products    ← 左側
categories  ← 右側

と考えます。

LEFT JOINでは、左側にあるproductsのデータを残したまま、categoriesを結合します。

categories側に一致するデータがあれば、そのカテゴリー情報を付けます。

一致するデータがなければ、categories側の値はNULLになります。

LEFT JOIN

左側のテーブルのデータをすべて残して、右側のテーブルを結合します。

FROM products
LEFT JOIN categories

なら、左側のproductsの商品はすべて結果に残ります。

結合するカテゴリーがなければ、カテゴリー側の値はNULLになります。

「LEFT」という名前だけを覚えるより、

左側を残すJOIN

と考えると分かりやすいです。

INNER JOINとLEFT JOINを比べてみよう

ここが今回一番大切なところです。

同じテーブルを同じ条件で結合しても、INNER JOINとLEFT JOINでは結果が変わることがあります。

INNER JOIN

FROM products AS p
INNER JOIN categories AS c
    ON p.category_id = c.category_id

結合条件に一致する商品だけが結果に残ります。

今回の場合、カテゴリーが設定されていないタブレットは表示されません。

LEFT JOIN

FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id

左側のproductsはすべて結果に残ります。

タブレットに対応するカテゴリーがなくても、商品自体は表示されます。

違いを表にすると、次のようになります。

JOIN	カテゴリーありの商品	カテゴリーなしの商品
INNER JOIN	表示される	表示されない
LEFT JOIN	表示される	表示される

INNER JOINとLEFT JOINの違い

INNER JOIN
→ 両方のテーブルで条件が一致するデータを取得

LEFT JOIN
→ 左側のデータはすべて取得し、一致する右側のデータがあれば結合

迷ったときは、

「結合相手がいないデータも表示したい？」

と考えてみましょう。

LEFT JOINの基本形を確認しよう

基本的な書き方は、INNER JOINとほとんど同じです。

SELECT カラム名
FROM テーブルA
LEFT JOIN テーブルB
    ON テーブルA.カラム = テーブルB.カラム;

今回なら、

FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id

です。

STEP12のINNER JOINから変わったのは、

INNER JOIN
    ↓
LEFT JOIN

の部分です。

ONで結合条件を指定するところは変わりません。

カテゴリーが決まっていない商品だけ探してみよう

LEFT JOINを使うと、結合相手が見つからなかったデータを探すこともできます。

今回なら、

「カテゴリーが設定されていない商品を探す」

という使い方です。

SELECT
    p.product_id,
    p.product_name,
    c.category_name
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
WHERE c.category_id IS NULL;

実行すると、タブレットが表示されます。

product_id | product_name | category_name
-----------+--------------+--------------
9          | タブレット     | NULL

ここで新しく登場したのが、

IS NULL

です。

NULLを条件にするときは、

category_id = NULL

とは書きません。

category_id IS NULL

と書きます。

NULLを調べるときはIS NULL

WHERE カラム名 IS NULL

とすると、値が入っていないデータを探せます。

今回なら、カテゴリーが設定されていない商品を見つけることができます。

NULLについては、ここで細かい仕組みまで覚える必要はありません。

まずは、

「NULLを探すときはIS NULL」

と覚えておきましょう。

LEFT JOINでもWHEREを使える

LEFT JOINした結果に対して、これまでと同じようにWHEREを使えます。

例えば、

「5000円以上の商品を、カテゴリーがなくても含めて表示したい」

なら、

SELECT
    p.product_name,
    p.price,
    c.category_name
FROM products AS p
LEFT JOIN categories AS c
    ON p.category_id = c.category_id
WHERE p.price >= 5000
ORDER BY p.price DESC;

と書けます。

カテゴリーが設定されていないタブレットも50000円なので、結果に含まれます。

ここでWHEREに指定しているのは、

p.price >= 5000

です。

つまり、左側のproductsにある価格を条件にしています。

WHEREの場所によって結果が変わることがある

LEFT JOINでは、WHEREの条件に少し注意が必要です。

例えば、

WHERE c.category_name = '周辺機器'

とすると、カテゴリー名が「周辺機器」の商品だけが残ります。

タブレットのcategory_nameはNULLなので、この条件には当てはまりません。

つまり、LEFT JOINを使っていても、その後のWHEREによってデータが結果から外れることがあります。

LEFT JOINなら必ず全部表示される、ではない

LEFT JOINは左側のデータを残して結合します。

ただし、その後にWHEREで条件を指定すれば、条件に合わないデータは結果から除外されます。

JOINとWHEREは、それぞれ別の役割を持っています。

このあたりはSQLに慣れてからも間違えやすいところです。

今の段階では、

LEFT JOINしたあとにWHEREでさらに絞り込まれることがある

と分かっていれば十分です。

どちらのJOINを使えばいい？

INNER JOINとLEFT JOINのどちらが優れている、ということではありません。

欲しい結果によって使い分けます。

例えば、

「カテゴリーが登録されている商品の一覧が欲しい」

ならINNER JOINが使えます。

一方、

「カテゴリー未設定の商品も含めた商品一覧が欲しい」

ならLEFT JOINが向いています。

【ルミィの吹き出し】

どっちが正解じゃなくて、「何を表示したいか」で選ぶんだね♪

SQLを書く前に、

「結合相手がないデータは結果に必要か？」

を考えてみると、選びやすくなります。

LEFT JOINは書く順番にも意味がある

LEFT JOINでは、どちらを左側にするかが重要です。

今回のSQLは、

FROM products AS p
LEFT JOIN categories AS c

なので、productsが左側です。

そのため、商品をすべて残します。

もし順番を逆にして、

FROM categories AS c
LEFT JOIN products AS p

とすれば、今度はcategoriesが左側になります。

つまり、カテゴリーをすべて残す考え方になります。

同じ2つのテーブルでも、どちらを左側に置くかで意味が変わります。

LEFT JOINを書くときは、

「どちらのデータを必ず残したいのか」

を考えてFROMを書くようにしましょう。

よくある間違い

INNER JOINとLEFT JOINの違いを構文だけで覚える

書き方だけを見ると、

INNER JOIN
LEFT JOIN

の違いしかありません。

大切なのは結果です。

INNER JOINは一致したデータ。

LEFT JOINは左側を残したデータ。

まずはこの違いを押さえましょう。

NULLを「= NULL」で調べる

NULLを探す場合は、

WHERE c.category_id = NULL

ではなく、

WHERE c.category_id IS NULL

と書きます。

LEFT JOINならWHEREを書いても全部残ると思う

LEFT JOINで左側の商品を残しても、その後のWHEREで条件に合わなければ結果から除外されます。

JOINでどう結合するのか。

WHEREでどのデータに絞るのか。

この2つを分けて考えましょう。

左右を意識せずにLEFT JOINする

FROM products AS p
LEFT JOIN categories AS c

なら、左側はproductsです。

「どちらのデータを残したいのか」を確認してから書くと、LEFT JOINの意味をつかみやすくなります。

STEP13のまとめ

STEP13のポイント

LEFT JOINは左側のテーブルのデータをすべて残す
結合相手がない場合、右側の値はNULLになる
INNER JOINは結合条件に一致するデータを取得する
INNER JOINとLEFT JOINは欲しい結果によって使い分ける
NULLを条件にするときはIS NULLを使う
LEFT JOINの後でもWHEREでデータを絞り込める
LEFT JOINでは、どちらのテーブルを左側にするかが重要

INNER JOINとLEFT JOINは、名前だけを見ると似ています。

ですが、

「結合相手がいないデータをどうする？」

と考えると違いが見えやすくなります。

商品とカテゴリーの例なら、

カテゴリーがある商品だけでいい
→ INNER JOIN

カテゴリーがない商品も一覧に残したい
→ LEFT JOIN

という使い分けです。

最初はこの判断ができれば十分です。

ルミィ

STEP13クリア！
INNER JOINとLEFT JOINの違いが見えてきたね。欲しい結果を考えて使い分けよう♪

次のSTEPへ

これでSQL入門で学ぶ新しい基本構文は、ほぼ出そろいました。

ここまでに、

SELECT、WHERE、ORDER BY、INSERT、UPDATE、DELETE、集計関数、GROUP BY、INNER JOIN、LEFT JOIN

と、少しずつできることを増やしてきました。

最後のSTEP14では、新しい構文を増やすのではなく、これまで学んだSQLを組み合わせます。

商品の登録・検索・更新・集計、そしてカテゴリーとの結合まで、自分でSQLを考えてデータを操作する総合演習にして仕上げましょう。

→ STEP14「SQLの基礎を使ってデータを操作しよう」

学習内容を確認したいときは、SQL入門の一覧から各STEPを確認できます。

→ SQL入門｜初心者が基礎から学べるSQL学習ガイド