副問い合わせとWITH句を比較して実感した、SQLの読みやすさの違い

はじめに

普段はReactやTypeScriptを中心にフロントエンド開発を行っている私ですが、
先日同僚からのデータ抽出依頼に応える形で、
PostgreSQLのSQLクエリを書く機会がありました。

その中で初めてWITH句（Common Table Expression）を使ってみたところ、副問い合わせ（サブクエリ）と比べて格段に読みやすく、保守性の高いクエリが書けたことに感動しました。

この記事では簡単なサンプルを通して、WITH句を使うことでSQLがどう読みやすくなるかを共有します。

WITH句で書いてみた

-- 「選」というキーワードを含み、かつ有効（status = 1）なキャンペーンを抽出。
-- さらに、タイトル中に含まれる「〇選」の数値部分を正規表現で抽出して declared_count として保持。
WITH valid_campaigns AS (
  SELECT
    cm.id,
    cm.title,
    cm.status,
    REGEXP_MATCHES(cm.title, '([0-9]+)選') AS declared_count -- タイトルから「〇選」の数値部分を抽出（配列形式）
  FROM
    campaign_menus cm
  WHERE
    cm.status = 1 -- 有効なキャンペーンのみ対象
    AND cm.title LIKE '%選%' -- 「選」を含むタイトルのみ対象
),

-- 上で抽出したキャンペーンに対して、
-- 実際に紐づいている有効なアイテム（status = 1）の件数をカウント。
-- 抽出された数値とアイテム数を比較するために集計。
campaign_with_items AS (
  SELECT
    vcm.id AS campaign_id,
    vcm.title,
    vcm.declared_count[1]::int AS declared_count, -- 抽出した文字列（配列の1番目）を数値に変換
    COUNT(i.id) AS actual_count -- 実際に紐づいているアイテム数（status = 1 のみカウント）
  FROM
    valid_campaigns vcm
    JOIN campaign_menu_items cmi ON vcm.id = cmi.campaign_menu_id
    JOIN items i ON cmi.item_id = i.id
  WHERE
    i.status = 1 -- 有効なアイテムのみカウント対象
  GROUP BY
    vcm.id, vcm.title, vcm.declared_count
)

-- 「タイトルに記載された数」と「実際に紐づくアイテム数」が一致していないキャンペーンのみを抽出。
SELECT
  *
FROM
  campaign_with_items
WHERE
  declared_count != actual_count;

サブクエリで書くとどうなる？

ちなみに、サブクエリ(副問合せ)を使って同じ意味のSQLを書くと以下のようになります。
複数のSQLが入れ子になる分、WITH句より可読性が落ちるのがわかると思います。

また、WITH句では各々の一時テーブルの意味が冒頭に来ることも、
可読性を上げる上で嬉しいポイントだと感じます。
(プログラミング言語の変数名に通じるものを感じます。
サブクエリだと名前は冒頭に来ないはずです。)

SELECT
  campaign_id,
  title,
  declared_count,
  actual_count
FROM (
  SELECT
    cm.id AS campaign_id,
    cm.title,
    CAST(
      (regexp_match(cm.title, '([0-9]+)選'))[1] AS INTEGER
    ) AS declared_count,
    COUNT(i.id) AS actual_count
  FROM
    campaign_menus cm
    JOIN campaign_menu_items cmi ON cm.id = cmi.campaign_menu_id
    JOIN items i ON cmi.item_id = i.id
  WHERE
    cm.status = 1
    AND i.status = 1
    AND cm.title ~ '([0-9]+)選'  -- 正規表現にマッチするタイトルのみ対象
  GROUP BY
    cm.id,
    cm.title,
    (regexp_match(cm.title, '([0-9]+)選'))[1]
) AS sub
WHERE
  declared_count IS NOT NULL
  AND declared_count != actual_count;

実行結果と確認方法

DB Fiddleで動作確認できます。

左側のSchema SQLにテーブル作成やデータ投入のためのSQLを書いて実行し、
右側のQuery SQLにデータ抽出を行うSQLを書いて実行する流れで動作確認しています。

以下のように、記事のタイトルから想定されるアイテム数と、実際に掲載されているアイテム数が異なる記事が抽出されるはずです。

campaign_id	title	declared_count	actual_count
2	5選！便利グッズ	5	4
3	2選！厳選アイテム	2	1

※DB FiddleはWEB上で簡単にDBの動きを確かめられる便利なツールです。
今回の場合はテーブルを作成してデータを投入し、それを抽出するという流れで動作確認しました。
(MySQL,PostgreSQL,SQLiteでの動作確認が可能です。)

まとめ・感想

• WITH句を使うことでSQLの意図が明確になり、保守性・可読性が向上した
• サブクエリだけでも同じ処理は書けるが、ネストが深くなると把握が難しくなる
• フロントエンドエンジニアでも、SQLの基本を少し知っておくと業務で役立つ場面が多くあります。
  • 例：きちんとデータが登録されているかの動作確認
  • API側のデータ構造を把握する際にも有効
• さらに、APIのコードを読みながらDBを確認できれば、自分の担当領域を広げる第一歩にもなります。

今後も複雑な集計や抽出を行う場面では、積極的にWITH句を使っていきたいと思います。