書く順番と、実行される順番は違う

SQLのわかりにくいエラーの大半は、書く順番と評価される順番が違うという一点から生まれます。SELECT を最初に書くのに、エンジンがそれを処理するのは後半です。だから次は失敗します。

SELECT price * quantity AS total
FROM order_items
WHERE total > 1000;          -- エラー: total なんて列は無い

一方、これは通ります。

SELECT price * quantity AS total
FROM order_items
ORDER BY total DESC;         -- OK

別名の書き方は何も変わっていません。変わったのは、その句が SELECT より前に走るか後に走るかだけです。

2つの順番を並べる

書く順番論理的な評価順
1. SELECT1. FROM / JOIN — 対象の行を組み立てる
2. FROM2. WHERE — 行を1件ずつ絞り込む
3. JOIN3. GROUP BY — 行をグループにまとめる
4. WHERE4. HAVING — グループを絞り込む
5. GROUP BY5. SELECT — 出力する列を計算する（別名はここで生まれる）
6. HAVING6. DISTINCT — 重複行を落とす
7. ORDER BY7. ORDER BY — 結果を並べ替える
8. LIMIT8. LIMIT / OFFSET — 結果を切り出す

右の列を上から読むだけで、SQLの「なぜ？」の多くは解消します。これは標準が定める論理的な順序で、実際の処理順はオプティマイザが結果さえ一致すれば自由に入れ替えます。

以下はすべて、この表から導かれる話です。

なぜWHEREでは別名が使えず、ORDER BYでは使えるのか

SELECT は5番目、WHERE は2番目です。WHERE が走る時点で別名はまだ存在していません。ORDER BY は7番目＝SELECT の後なので、その時点では存在しています。

移植性のある回避策は、式をそのまま書き直すことです。

SELECT price * quantity AS total
FROM order_items
WHERE price * quantity > 1000;

あるいはサブクエリに押し込めば、外側から見て別名が実在の列になります。

SELECT * FROM (
    SELECT price * quantity AS total FROM order_items
) t
WHERE t.total > 1000;

この規則をどこまで緩めるかは方言によります。MySQLは GROUP BY と HAVING で別名を使えます。PostgreSQLは GROUP BY と ORDER BY では使えますが HAVING では使えません。ただし WHERE で別名を許すデータベースは主要なものには存在しません。 1つだけ覚えるならこれです。

WHERE と HAVING の使い分け

この2つは交換できません。理由はやはり順序です。WHERE（2番目）はグループ化する前の行を絞り、HAVING（4番目）は集計後のグループを絞ります。

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'          -- まず行を絞る: 支払い済みの注文だけ
GROUP BY customer_id
HAVING COUNT(*) >= 3;          -- 次にグループを絞る: それが3件以上

入れ替えると意味が完全に変わります。HAVING status = 'paid' は行ではなくグループに対する問いになってしまい、逆に WHERE COUNT(*) >= 3 はエラーです。2番目の時点ではまだ何も数えていないからです。

実務的な判断基準は単純で、条件に集計関数が出てくるなら HAVING、出てこないなら WHERE。集計を使わない条件を WHERE に置いておけば、エンジンが早い段階で行を捨てられるので速度の面でも有利です。

GROUP BY：データベースで挙動が違う規則

グループ化するとき、SELECT に書く列は**GROUP BY に含まれているか、集計関数で包まれているか**のどちらかでなければなりません。そうでないと、まとめられた複数行のうちどれを表示すべきか決められないからです。

-- 壊れている: どの name をカウントと並べるのか決まらない
SELECT customer_id, name, COUNT(*) FROM orders GROUP BY customer_id;

PostgreSQLはこれを明確に拒否します。MySQLも既定で拒否します——5.7.5以降は ONLY_FULL_GROUP_BY モードが最初から有効だからです。古い設定のMySQLはこれを受け付けて適当な1行の値を返していたため、サーバーを新しくした途端に既存クエリが動かなくなるという形で表面化します（expression is not in GROUP BY clause and contains nonaggregated column）。対処はモードを切ることではなく、列を GROUP BY に足すか MAX(name) のように集計することです。

集計とNULL

数値が静かに変わるので、次の2点は覚えておく価値があります。

式NULLを数えるか
COUNT(*)数える（行数そのもの）
COUNT(列)数えない（その列がNULLの行は飛ばす）
COUNT(DISTINCT 列)数えない（NULLでない異なる値の数）
SUM / AVG / MAX / MIN数えない（NULLは無視）

AVG(列) は行数ではなくNULLでない値の件数で割ります。NULLを0として扱いたいなら AVG(COALESCE(列, 0)) と明示してください。

これが一番効いてくるのが LEFT JOIN の後です。一致しなかった行は右側がNULLになるため、COUNT(*) だと注文が0件の顧客も1と数えてしまいます。COUNT(o.id) なら正しく0になります。

SELECT c.id, COUNT(o.id) AS order_count      -- COUNT(*) ではない
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

LIMITは最後に走る。そして同着に弱い

LIMIT は8番目、並べ替えの後に適用されます。ここから2つの帰結があります。

集計が軽くなるわけではない。 GROUP BY を含むクエリに LIMIT 10 を付けても、全体をグループ化してから大半を捨てるだけです。

一意なソートキーが無いとページングが壊れる。 created_at に同着があると、ページをめくる過程で同じ行が2回出たり、逆に一度も出なかったりします。一意な列をタイブレークに足してください。

ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 40;

構文は方言差があります。PostgreSQLとMySQLはどちらも LIMIT n OFFSET m を受け付けます（MySQLには引数が逆順の古い LIMIT m, n もあります）。SQL Serverは OFFSET m ROWS FETCH NEXT n ROWS ONLY です。

JOIN と WHERE の罠

順序の帰結でもっとも事故が多いのがこれです。JOIN は1番目、WHERE は2番目——つまり右テーブルに対する WHERE の条件は、外部結合がNULLを埋めた後に走り、そのNULL行は条件を満たさず消えます。

-- 実質的にINNER JOINになってしまう
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- 支払い済み注文が無い顧客も残る
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';

結合を絞る条件は ON に、最終結果を絞る条件は WHERE に書きます。JOIN種類ごとの結果行数の違いは SQL JOIN完全リファレンス にまとめています。

順番を暗記せずに組み立てる

順番を知っているのと、毎回正しい位置に句を書けるのは別の話です。Visual SQL Builder はテーブル・結合条件・絞り込み・グループ化・並べ替えをフォームから選ぶと、正しい記述順で句を出力します。要素を足すたびに生成SQLが変わるのが見えるので、「この条件はどこに置くのが正しいか」を確認する用途にも使えます。

既存クエリを読むときは SQLフォーマッター で整形すると句の境界が見えるようになり、SQL to ER図 変換 を使えば結合しているテーブル同士の関係を図で確認できます。テーブル設計の段階なら CREATE TABLE リファレンス が型と制約をまとめています。いずれもブラウザ内で完結し、貼り付けたクエリやスキーマが外部に送信されることはありません。

まとめ

  • 評価順は FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
  • 別名は SELECT で生まれるので WHERE からは見えず、ORDER BY からは見える
  • 集計を含まない条件は WHERE、集計を含む条件は HAVING
  • MySQLは5.7.5以降 ONLY_FULL_GROUP_BY が既定。選択した列は集計するかグループ化する
  • COUNT(*) は行数、COUNT(列) はNULLを飛ばす。LEFT JOIN の後はほぼ後者が正しい
  • LIMIT は最後。安定したページングには ORDER BY に一意なタイブレークが要る
  • 結合を絞るなら ON、結果を絞るなら WHERE

よくある質問

別名がORDER BYでは使えてWHEREでは使えないのはなぜですか？

WHERE は SELECT より前に、ORDER BY は後に評価されるためです。別名は SELECT が作るので、WHERE が走る時点ではまだ存在しません。WHERE には式をそのまま書くか、サブクエリで包んで別名を実在の列に変えてください。

WHEREとHAVINGはどう使い分けますか？

WHERE はグループ化前の個々の行を、HAVING は集計後のグループを絞ります。条件に COUNT() や SUM() などの集計関数が出てくるなら HAVING に置くしかありません。それ以外は WHERE に置いたほうが、早い段階で行を捨てられるぶん有利です。

MySQLで「expression is not in GROUP BY clause」と出ます

MySQL 5.7.5以降で既定有効の ONLY_FULL_GROUP_BY によるものです。SELECT に書いた列はすべて GROUP BY に含めるか集計関数で包む必要があります。古いMySQL向けに書かれたクエリがアップグレードで落ちる典型例なので、モードを無効化するのではなく、列を GROUP BY に足すか集計するのが本筋です。

LIMITを付ければ集計クエリは速くなりますか？

基本的になりません。LIMIT はグループ化と並べ替えがすべて終わった後に適用されるため、データベースは結果全体を作ってから捨てています。軽くしたいなら WHERE で先に対象を減らすか、グループ化を助けるインデックスを検討してください。

貼り付けたSQLはサーバーに送信されますか？

いいえ。Visual SQL Builder と SQLフォーマッター はどちらもブラウザ内で完結し、入力したクエリやテーブル名が外部へ送信されることはありません。