【中級者向け】SQLのクエリの品質を上げる

はじめに

「SQLのクエリは書けるけど、安全でパフォーマンスを意識したクエリが書けるようになりたい」という人を対象に記事を書きました。クエリの品質を向上させるために、最低限意識しておくべきポイントをまとめます。

1. SELECTするのは必要なカラムのみにする

SELECT *は避けましょう。I/O負荷の増加や、メモリ使用量の増加により実行コストがかかってしまいます。また、SELECT *でのUNIONにおいてはバグの原因にもなります。UNIONは型さえ合っていればエラーにならないため、以下のクエリでTABLE_AとTABLE_Bのカラムの並びが違うと意図しない結果になります。

SELECT * FROM TABLE_A
UNION ALL
SELECT * FROM TABLE_B

2. CASE文は必ずELSEを書く

暗黙的にNULLになりますが、「エラーにはならないが結果が違う」という厄介なバグに繋がることがあります。例えば以下のようなユーザテーブルがあるとします。

id	first_name	last_name
1	太郎	田中
2	NULL	鈴木
3	健二	NULL
4	NULL	NULL

このユーザテーブルから以下のようにメールの件名を作るとします。

SELECT
    CASE
        WHEN first_name IS NOT NULL AND last_name
            THEN last_name + first_name + '様にポイントプレゼント'
        WHEN first_name IS NULL AND last_name IS NOT NULL
            THEN last_name '様にポイントプレゼント'
        WHEN first_name IS NOT NULL AND last_name IS NULL
            THEN first_name '様にポイントプレゼント'
    END AS title
FROM users;

first_nameとlast_nameの両方がNULLのデータは存在しない想定で上記のクエリを書いてしまうと、ID=4のユーザは考慮上記で考慮されていないので件名がNULLなってしまいます。
以下のようにELSEをつけましょう。

SELECT
    CASE
        WHEN first_name IS NOT NULL AND last_name
            THEN last_name + first_name + '様にポイントプレゼント'
        WHEN first_name IS NULL AND last_name IS NOT NULL
            THEN last_name '様にポイントプレゼント'
        WHEN first_name IS NOT NULL AND last_name IS NULL
            THEN first_name '様にポイントプレゼント'
        ELSE 'ポイントプレゼント'
    END AS title
FROM users;

3. 列番号は使わない

ORDER BY 1,2,3のように列番号を使うことができますが、テーブルの構造やクエリ改修によって意図しない挙動になる可能性があるので列番号の使用は避けましょう。

4. できるだけ早い段階でレコード数を絞る

レコード数はできるだけ早い段階で絞り込むことを意識しましょう。特にレコード数が膨大なテーブルを扱う場合は注意が必要です。
例えば、過去のメール配信の実績テーブル(delivered_history)があるとします。メールの配信対象者のうち、過去1ヶ月以内に配信した対象者は除外するという要件があり以下のクエリを書いたとします。

WITH delivered_members AS (
    SELECT member_id
    FROM delivered_history
    WHERE delivered_on >= (CURRENT_DATE - INTERVAL '1 month')
)
SELECT member_id
FROM target_members
LEFT OUTER JOIN delivered_members USING(member_id)
WHERE delivered_members.member_id IS NULL -- 1ヶ月以内に配信してないユーザに絞る

target_membersが数十万レコードで、delivered_historyが過去1ヶ月に配信したユーザのレコードが1億レコードあるようなとても大きなテーブルだと、delivered_membersのLEFT OUTER JOINでコストがかなりかかります。そこで、delivered_membersがもっと絞れないか考えます。過去1ヶ月に配信したユーザを全て抽出する必要はなく、過去1ヶ月かつ今回の対象者だけに絞れば目的は果たせます。コストを抑えるクエリは以下のようになります。

WITH delivered_members AS (
    SELECT member_id
    FROM delivered_history
    WHERE DELIVERED_ON >= (CURRENT_DATE - INTERVAL '1 month')
    /* 対象者のみに絞る */
    AND EXISTS (
        SELECT 1
        FROM target_members
        WHERE delivered_history.member_id = target_members.member_id
    )
)
SELECT member_id
FROM target_members
LEFT OUTER JOIN delivered_members USING(member_id)
WHERE delivered_members.member_id IS NULL -- 1ヶ月以内に配信してないユーザに絞る

こうすることでdelivered_membersは数十万件以内まで絞れるためLEFT OUTER JOINのコストが大幅に減少します。

5. NOT IN演算子は極力使用しない

NOT INはNULLを適切に扱わないため、NULLを含む場合、サブクエリの結果がNULLと比較され、その結果が不確定（UNKNOWN）となり、最終的に条件が成立しないことになります。例えば、以下のような製品テーブル(products)と除外カテゴリテーブル(excluded_categories)があるとします。

productsテーブル

id	name	category
1	Product A	Electronics
2	Product B	Clothing
3	Product C	Electronics
4	Product D	Clothing

excluded_categoriesテーブル

category
Clothing
NULL

製品データから除外カテゴリを除外するためにNOT INを使って以下のクエリを書いたとします。

SELECT id, name, category
FROM products
WHERE category NOT IN (SELECT category FROM excluded_categories);

このクエリでは、excluded_categoriesテーブルのcategoryにNULLが含まれているため、サブクエリのNOT IN (SELECT category FROM excluded_categories) は、NULL を含む結果を扱う際に問題が発生します。具体的にはSQLエンジンで以下のように評価されるためです。

(products.category = 'Clothing' または products.category = NULL)ではない
↓
products.category != 'Clothing' かつ products.category != NULL

ここで!= NULLが問題になります。NULLとの比較結果は不確定（UNKNOWN）として扱われるため結果を返しません。極力NOT EXISTSを使いましょう。

さいごに

今回はSQLのクエリを書く上で最低限意識しておくべきポイントをまとめました。他にもインデックスの効かせ方等はありますが、これらを意識するだけでもかなり品質は上がるので是非意識してみましょう。