注文画面に商品名が出る理由
SQLの JOIN は、外部キーでつながった2つの表を、1つの結果にまとめる命令です。商品と、その商品のカテゴリ名を1つの表のように読めます。
ネットショップの注文表には、商品の番号しか入っていません。それでも画面に商品名が出るのは、裏で JOIN が表をつないでいるからです。
このレッスンのゴール
メモ
2つの表を、JOIN でつなげられる。
キーワード
| ことば | いみ | れい |
|---|---|---|
| 外部キー | 別の表の行を指す列 | club_id |
| JOIN | 2つの表を、同じ値の列でつなぐ操作 | |
| ON | どの列と列が同じなら、つなぐかを決める句 | |
| 別名（エイリアス） | 表や列に付ける短い呼び名 | AS club |
| INNER JOIN | 相手の行がある行だけを残すつなぎ方 | |
| LEFT JOIN | 左の表の行を、相手がなくても残すつなぎ方 |
学生が入力するセルは、このプレビューでは空白です。
なぜ表を分けるのか
部員の表に、部活の名前と部屋を毎回書くと、同じ文字が何十回も並びます。部屋が変わったら、何十行も直すことになります。
だから、部活は部活の表に1行だけ置き、部員の表には部活の番号（外部キー）だけを入れます。読むときに、JOIN でつなぎ直します。
例: 分けた2つの表
| members の id | name | club_id | 指す clubs の行 |
|---|---|---|---|
| 1 | 田中 | 1 | サッカー部（グラウンド） |
| 2 | 佐藤 | 2 | 美術部（美術室） |
| 5 | 伊藤 | NULL | どの部活も指さない |
学生が入力するセルは、このプレビューでは空白です。
試す: 部員と部活をつなぐ
-- 部員と、その部員の部活の名前を並べます。
SELECT members.name, clubs.name AS club
FROM members
JOIN clubs ON members.club_id = clubs.id
ORDER BY members.id;
-- 伊藤さん（部活なし）が出ないことを確かめましょう。
JOIN と ON の書き方
FROM members JOIN clubs ON members.club_id = clubs.id と書きます。ON の右には、つなぐ列どうしの等式を置きます。
2つの表に同じ名前の列（name）があるときは、clubs.name のように、表の名前を前に付けます。長いときは AS で別名を付けます。
始める前に
メモ
上のバーの Start the lab を押し、マシンが動き出すまで待ってください。ターミナルを開き直したときは、最初の2つの Run をもう一度押します。
証明書を受け入れる設定
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
ショップ用の短い名前
alias sql='mariadb -h db -u student -pstudent shop -t -e'ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
商品とカテゴリの名前
sql "SELECT p.name AS product, c.name AS category FROM products p JOIN categories c ON c.id = p.category_id ORDER BY p.id LIMIT 8"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
表にも別名を付けられる
上の Run では、products p のように、表に短い別名を付けました。以降は p.name と書けます。
別名は書く量を減らし、どの表の列かをはっきりさせます。結果の列には AS で、読みやすい題を付けられます。
商品と仕入れ先の町
sql "SELECT p.name, s.city FROM products p JOIN suppliers s ON s.id = p.supplier_id WHERE s.country = 'Japan' ORDER BY p.id"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
相手のいない行はどうなるのか
普通の JOIN（INNER JOIN）は、相手がいる行だけを返します。部活のない部員は、結果から消えます。
消したくないときは LEFT JOIN を使います。左の表の行は全部残り、相手がいない列は NULL になります。
試す: 部活のない部員も残す
-- 部活のない部員も残します。
SELECT members.name, clubs.name AS club
FROM members
LEFT JOIN clubs ON members.club_id = clubs.id
ORDER BY members.id;
-- 部員が1人もいない部活を探すには、表の順番をどう変えますか。
カテゴリのない商品
sql "SELECT p.name FROM products p LEFT JOIN categories c ON c.id = p.category_id WHERE c.id IS NULL ORDER BY p.name"ここでは表示のみで、実行されません。無料アカウントを作成すると、実行ボタンで自分のラボのターミナルに入力できます。
よくある間違い
注意
ON を書き忘れると、2つの表の全部の組み合わせが返ります。表が100行と200行なら、2万行です。結果が多すぎたら、まず ON を確かめます。
例: 2つのつなぎ方の違い
| 書き方 | 相手がいない行は | 部員8人の例の行数 |
|---|---|---|
| JOIN（INNER JOIN） | 消える | 7行 |
| LEFT JOIN | 残り、相手の列は NULL | 8行 |
学生が入力するセルは、このプレビューでは空白です。
さらに挑戦: 部員のいない部活
ショップで、レビューが1件もない商品を探します。products を左、reviews を右にして LEFT JOIN し、reviews.id IS NULL の行を残します。
このクエリを書き、結果の件数を数えてください。なぜ reviews.product_id IS NULL でも同じ結果になるかを、一言で説明してください。