体系ガイド · 入門

SQLを、行の集合と結合の結果から考える

SELECTの書き方だけでなく、条件、結合、集計で行がどう変わるかを追い、重複やNULLによる誤りを防ぎます。

この記事をロゲイアに質問する確認問題3問に進むこれまでの誤答を復習する

更新日

この記事の目次
  1. 表の見た目より、行の意味を決める
  2. 結合は、対応する行の組を作る
  3. NULLを含む行で確かめる
  4. 正しいSQLと速いSQLを分けて検証する
  5. 3人の注文数を、小さな入力で確かめる
  6. この記事をロゲイアに質問する理解を確かめる

表の見た目より、行の意味を決める

利用者の表と注文の表を考えます。利用者はuser_idで識別され、注文にはorder_idと購入者のuser_idが入ります。主キーは行を一意に識別し、外部キーは参照先との関係を制約します。列名が似ているだけで、自動的に正しい関係が成立するわけではありません。

SQLで取得される結果には、同じ値の行が複数現れることがあります。「集合だから重複は絶対にない」と思い込むと、集計を誤ります。主キー、必要な列、重複を認めるかどうかを、要求の意味に照らして決めましょう。表示順が必要な場合はORDER BYで明示し、保存順や以前の実行結果に依存しません。

結合は、対応する行の組を作る

usersとordersをuser_idで結合すると、一人の利用者に注文が3件あれば、その人の情報を持つ行が3行できます。これは結合の失敗ではなく、一対多という関係の結果です。「利用者の人数」をそのままCOUNT(*)で数えると、注文がある利用者の件数ではなく、結合後の行数になる点に注意が必要です。

一人ずつの注文数が必要なら、利用者単位にまとめて集計します。注文がない利用者も残すならLEFT JOINを選びます。さらに、注文側の列に対する条件をWHEREへ置くと、NULLで補われた行が落ち、意図した外部結合の結果と異なることがあります。条件をONとWHEREのどちらへ置くかは、残したい行から判断します。

sql
SELECT u.user_id, COUNT(o.order_id) AS order_count
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.user_id
GROUP BY u.user_id
ORDER BY u.user_id;

NULLを含む行で確かめる

NULLは、通常の値と同じように等号で比較できるものではありません。列がNULLかを調べるにはIS NULLを使います。WHEREは条件が真となる行を残すため、比較結果が不明になる行も、そのままでは残りません。例えば、値が不明な年齢はage >= 18という条件を満たすと確認できません。

上の例ではCOUNT(o.order_id)が、注文のない利用者に対して0を返せます。order_idが主キーでNULLを許さないとき、外部結合で補われたNULLを数えないためです。一方、COUNT(*)は結合後に存在する行自体を数えます。両者を小さな表で実際に比べると、文法の暗記よりも違いを理解しやすくなります。

正しいSQLと速いSQLを分けて検証する

まず0件、1件、複数件、NULL、重複する表示名などの入力を用意し、結果の意味を確認します。その後で、索引や実行計画を使って性能を調べます。速く実行できても、注文を利用者として数えるような意味の誤りがあれば、要求を満たしていません。

ここで説明した処理順は、結果の意味を考えるための整理です。データベースが内部でその順番どおりに物理処理するとは限りません。また、PostgreSQLの文法や挙動を他製品へそのまま一般化せず、製品ごとの差、型変換、照合順序、制約を確認しましょう。

3人の注文数を、小さな入力で確かめる

利用者を1、2、3の3人とし、注文を「注文10は利用者1」「注文11は利用者1」「注文12は利用者2」の3件とします。LEFT JOIN後の行は、(1,10)、(1,11)、(2,12)、(3,NULL)の4行です。利用者ごとにまとめてCOUNT(o.order_id)を取ると、結果は(1,2)、(2,1)、(3,0)です。利用者3の行も結合結果には1行あるため、COUNT(*)に置き換えると最後が1になり、注文数という要件を満たしません。

次に「注文がない人も残し、完了注文だけを数える」という要件を追加します。注文側のstatus = 'paid'をON条件へ加えれば、条件に合う注文がない利用者にもNULLを補った行が残ります。同じ条件をWHEREへ置くと、そのNULL行は条件を満たさず消えます。どちらが速いかを検討する前に、利用者3を含む期待結果を紙に書いて比較しましょう。ここで示した行の追跡は問い合わせの意味の説明で、物理的な結合アルゴリズムの指定ではありません。

読了と問題の結果を、言語ごとにこのブラウザへ保存します。

コースの順番に沿って学ぶ

サーバーの中で起きていること · STEP 11

学習コースから次を選ぶ