はじめに
ちょっと色々あってコーディングテストを受ける機会がありました。
その中でSQL文を書くテストがあったのですが、普段の業務では後ろでNoSQL文が動く特殊関数を使用しているため、MySQLを直接書く機会がほとんどありません。
SQL文について全く知らないわけではないものの、いざ自分で書こうとすると、
-
GROUP BYってどんなときに必要だっけ？ -
WHEREとHAVINGの違いは？ -
INNER JOINとLEFT JOINの使い分けは？
など、曖昧な部分が多かったので、テスト前に最低限押さえた内容をまとめてみました。
SQLを網羅的に学ぶというより、問題文を見て基本的なSQLを書けるようになるための自分用メモになります。
SQLの基本形
SQLは、以下のような順番で記述する。
SELECT 取得する列
FROM 基準となるテーブル
JOIN 結合するテーブル
ON 結合条件
WHERE 行を絞り込む条件
GROUP BY グループ化する列
HAVING 集計後の条件
ORDER BY 並び替える列
LIMIT 取得件数;
ただし、SQLが内部で処理される順番は記述順とは異なる。
FROM・JOIN
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
ORDER BY
↓
LIMIT
WHEREの処理時点ではまだSELECTが実行されていないため、SELECTで付けた別名をWHEREで使用できない。
SELECT
SELECTは、テーブルから取得する列を指定する。
SELECT name, price
FROM books;
すべての列を取得する場合は*を使用する。
SELECT *
FROM books;
列に別名を付ける
ASを使用すると、取得結果の列に別名を付けられる。
SELECT
name,
price,
price * 0.1 AS tax
FROM books;
AS自体は省略可能。
price * 0.1 tax
WHERE
WHEREは、取得する行を条件で絞り込むときに使用する。
SELECT *
FROM books
WHERE price >= 1000;
よく使用する条件は以下。
-- 価格が1000円
WHERE price = 1000
-- 価格が1000円ではない
WHERE price <> 1000
-- 価格が1000円以上
WHERE price >= 1000
-- 名前に「SQL」を含む
WHERE name LIKE '%SQL%'
-- category_idが1、2、3のいずれか
WHERE category_id IN (1, 2, 3)
-- 指定した範囲内
WHERE price BETWEEN 1000 AND 3000
LIKE
LIKEは文字列を部分一致で検索するときに使用する。
-- 「SQL」で始まる
WHERE name LIKE 'SQL%'
-- 「SQL」で終わる
WHERE name LIKE '%SQL'
-- 「SQL」を含む
WHERE name LIKE '%SQL%'
%は、0文字以上の任意の文字列を表す。
NULLの判定
NULLは、値が存在しない状態を表す。
通常の値のように=では比較できない。
-- 間違い
WHERE deleted_at = NULL
NULLかどうかを確認する場合はIS NULLを使用する。
WHERE deleted_at IS NULL
NULLではないデータを取得する場合はIS NOT NULL。
WHERE deleted_at IS NOT NULL
ANDとOR
複数の条件を組み合わせる場合は、ANDやORを使用する。
SELECT *
FROM books
WHERE category_id = 1
AND price >= 1000;
ANDとORを同時に使用する場合は、条件の優先順位に注意。
ANDはORより先に評価される。
WHERE category_id = 1
AND price < 1000
OR price >= 5000
上記は、以下のように評価される。
WHERE (category_id = 1 AND price < 1000)
OR price >= 5000
意図した条件を明確にするため、迷ったらカッコを付けた方がよさそう。
WHERE category_id = 1
AND (price < 1000 OR price >= 5000)
ORDER BY
ORDER BYは、取得結果を並び替えるときに使用する。
SELECT *
FROM books
ORDER BY price ASC;
ASCは昇順、DESCは降順。
-- 価格が安い順
ORDER BY price ASC
-- 価格が高い順
ORDER BY price DESC
ASCは省略が可能。
ORDER BY price;
複数の条件を指定する場合は以下。
ORDER BY category_id ASC, price DESC;
LIMIT
LIMITは、取得する件数を制限する。
SELECT *
FROM books
LIMIT 3;
DISTINCT
DISTINCTは、重複するデータを除外する。
SELECT DISTINCT category_id
FROM books;
複数の列を指定した場合は、それぞれの列ではなく列の組み合わせで重複が判定される。
SELECT DISTINCT category_id, name
FROM books;
JOIN
複数のテーブルに分かれているデータを取得する場合は、JOINを使用する。
例：books.idとbook_sales.book_idが紐づいている2つのテーブルを結合する場合
books
| id | name |
|---|---|
| 1 | SQL入門 |
| 2 | Ruby入門 |
| 3 | JavaScript入門 |
book_saless
| id | book_id | price |
|---|---|---|
| 1 | 1 | 1500 |
| 2 | 2 | 2000 |
INNER JOIN
INNER JOINは、両方のテーブルに対応するデータが存在する行だけを取得。
SELECT
books.name,
book_sales.price
FROM books
INNER JOIN book_sales
ON books.id = book_sales.book_id;
INNER JOINは、JOINと省略可能。
SELECT
books.name,
book_sales.price
FROM books
JOIN book_sales
ON books.id = book_sales.book_id;
→売上情報が存在しない「JavaScript入門」は取得されない。
LEFT JOIN
LEFT JOINは、左側のテーブルのデータをすべて取得。
右側に対応するデータがない場合は、右側の列にNULLが入る。
SELECT
books.name,
book_sales.price
FROM books
LEFT JOIN book_sales
ON books.id = book_sales.book_id;
→売上情報が存在しない「JavaScript入門」も取得される。
- 両方にデータが存在するものだけ取得する：
INNER JOIN - 左側のデータをすべて残す：
LEFT JOIN
JOIN条件
テーブルを結合するときは、主キーと外部キーを確認する。
ON books.id = book_sales.book_id
集計関数
複数の行を集計するときは、集計関数を使用する。
よく使用するものは以下。
| 関数 | 内容 |
|---|---|
COUNT() |
件数 |
SUM() |
合計 |
AVG() |
平均 |
MAX() |
最大値 |
MIN() |
最小値 |
COUNT
行数を取得します。
SELECT COUNT(*) AS book_count
FROM books;
COUNT(*)はすべての行を数える。
COUNT(*)
列名を指定した場合は、指定した列がNULLではない行だけを数える。
COUNT(price)
たとえば5行のうち、priceがNULLの行が1件存在する場合は以下になる。
COUNT(*) → 5
COUNT(price) → 4
SUM
数値の合計を取得する。
SELECT SUM(price) AS total_price
FROM book_sales;
AVG
数値の平均を取得する。
SELECT AVG(price) AS average_price
FROM book_sales;
MAX・MIN
最大値と最小値を取得する。
SELECT
MAX(price) AS max_price,
MIN(price) AS min_price
FROM book_sales;
GROUP BY
同じ値を持つデータをグループ化して集計するときに使用する。
例：カテゴリーごとの本の件数を取得する場合
SELECT
category_id,
COUNT(*) AS book_count
FROM books
GROUP BY category_id;
SELECTに指定できる列
GROUP BYを使用する場合、SELECTに指定できるのは基本的に以下のどちらか。
-
GROUP BYに指定した列 -
COUNT()やSUM()などで集計した列
SELECT
price,
COUNT(*) AS book_count
FROM books
GROUP BY price;
SELECT
name,
price,
SUM(price)
FROM books
GROUP BY price;
→同じ価格の本が複数存在した場合、priceごとにグループ化しているのに、nameも取得しようとしているため、どのnameを表示すればよいか判断できないためエラーになる。
GROUP BYが必要なのかを確認する
例：書籍名と価格、消費税の一覧を取得する場合
SELECT
books.name,
book_sales.price,
book_sales.price * 0.1 AS tax
FROM books
JOIN book_sales
ON books.id = book_sales.book_id;
→単純な一覧取得なので、SUM()やGROUP BYは必要ない。
問題文に以下のような言葉がある場合は、集計が必要かを考える。
- 件数
- 合計
- 平均
- 最大
- 最小
- カテゴリーごと
- 書籍ごと
反対に、単純に「一覧を取得」と書かれている場合は、集計しなくてよい可能性が高そう。
WHEREとHAVINGの違い
どちらもデータを絞り込むために使用するが、絞り込むタイミングが違う。
個々のデータを絞る → WHERE
集計した結果を絞る → HAVING
WHERE
WHEREは、グループ化する前の行を絞り込む。
SELECT
category_id,
COUNT(*) AS book_count
FROM books
WHERE price >= 1000
GROUP BY category_id;
→価格が1000円以上の本だけを対象に、カテゴリーごとの件数を集計。
HAVING
HAVINGは、グループ化した後の集計結果を絞り込む。
SELECT
category_id,
COUNT(*) AS book_count
FROM books
GROUP BY category_id
HAVING COUNT(*) >= 5;
→本が5冊以上存在するカテゴリーだけを取得。
CASE
CASEを使用すると、条件によって取得結果を変更できる。
SELECT
name,
price,
CASE
WHEN price >= 5000 THEN '高価格'
WHEN price >= 1000 THEN '通常'
ELSE '低価格'
END AS price_category
FROM books;
CASEは上から順番に条件が判定される
そのため、以下のように「条件の範囲が広いもの」や「数値が小さいもの」から書くと、後ろの条件まで到達しない。
（5000円以上の商品も最初のprice >= 1000に該当するため、すべて「通常」になる。）
よって、条件の範囲が狭いものや、数値が大きいものから書く必要がある。
CASE
WHEN price >= 1000 THEN '通常'
WHEN price >= 5000 THEN '高価格'
END
サブクエリ
SQLの中に別のSQLを書くことをサブクエリと呼ぶ。
サブクエリは、まず内側のSQLを実行する。その結果を外側のSQLで使用する。
例：平均価格以上の本を取得する場合
SELECT
name,
price
FROM books
WHERE price >= (
SELECT AVG(price)
FROM books
);
問題文から使用するSQLを判断する
よくある問題文に出てくる表現と、使用するSQLのまとめ。
| 問題文 | 使用するSQL |
|---|---|
| 一覧を取得 | SELECT |
| 条件を満たす | WHERE |
| ○○を含む | LIKE '%○○%' |
| 重複を除く | DISTINCT |
| 件数 | COUNT() |
| 合計 | SUM() |
| 平均 | AVG() |
| ○○ごと | GROUP BY |
| 集計結果が○件以上 | HAVING |
| 高い順 | ORDER BY ... DESC |
| 安い順 | ORDER BY ... ASC |
| 上位3件 |
ORDER BYとLIMIT 3
|
| 関連するデータを取得 | JOIN |
| 関連データがなくても含める | LEFT JOIN |
問題を解くときの考え方
いきなりSQLを書き始めると混乱しそうなので、まず問題文を日本語で分解する。
例：書籍ごとの売上合計を、売上が高い順に取得する場合
- 必要な情報を整理する。
取得する列：書籍名、売上合計
使用するテーブル：books、book_sales
テーブルの結合：books.id = book_sales.book_id
集計する単位：書籍ごと
集計方法：SUM
並び順：売上合計の降順
- SQLに書き換える。
SELECT
books.name,
SUM(book_sales.price) AS total_sales
FROM books
JOIN book_sales
ON books.id = book_sales.book_id
GROUP BY books.id, books.name
ORDER BY total_sales DESC;
1. 使用するテーブルを書く
SELECT *
FROM books;
2. テーブルを結合する
SELECT *
FROM books
JOIN book_sales
ON books.id = book_sales.book_id;
3. 必要な列だけにする
SELECT
books.name,
book_sales.price
FROM books
JOIN book_sales
ON books.id = book_sales.book_id;
4. 集計や並び替えを追加する
SELECT
books.name,
SUM(book_sales.price) AS total_sales
FROM books
JOIN book_sales
ON books.id = book_sales.book_id
GROUP BY books.id, books.name
ORDER BY total_sales DESC;
間違えやすそうな部分
不要なGROUP BYを使用する
一覧を取得するだけなのに、なんとなくGROUP BYを付けないようにする。
-- 一覧取得なら不要な可能性が高い
GROUP BY price
SELECTとGROUP BYの列が合っていない
SELECT
name,
category_id,
COUNT(*)
FROM books
GROUP BY category_id;
→nameがグループ化も集計もされていない。
JOINする列を間違える
-- 主キーと主キーを結合してしまっている
ON books.id = book_sales.id
ON books.id = book_sales.book_id
NULLにイコールを使用する
-- 間違い
WHERE deleted_at = NULL
-- 正しい
WHERE deleted_at IS NULL
まとめ
コーディングテスト前に、ひとまず以下を優先して確認しました。
-
SELECT、WHERE、ORDER BY -
INNER JOIN、LEFT JOIN -
COUNT()、SUM()、AVG() -
GROUP BY、HAVING CASE- サブクエリ
特に大事そうなのは、問題文を読んだ段階で、
- 単純な一覧取得なのか
- 複数のテーブルを結合するのか
- データを集計するのか
- 集計前と集計後のどちらを絞り込むのか
を整理することでした。
SQLの書き方だけを暗記するより、まず「何を取得したい問題なのか」を分解した方が問題自体を理解できるかなと思いました。
まだ複雑なSQLをすらすら書ける状態ではないものの、基本問題で何を使えばよいかは少し整理できました。