WHERE 句

インデックス

WHERE句では数値のデータか日付が格納されているカラムを使う
WHERE句で使われるカラムにインデックスが設定済みかどうか

like

WHERE句でlikeは使わない
部分一致はDBではなく、検索エンジンを使う

日付

日付のカラムは閾値前後の値を確認しておく

日付のカラムは文字列ではなく、dateを使ってチェックする
xxx_date BETWEEN date('2005-01-01 00:00:00') AND date('2005-12-31 23:59:59')

上限

SQLの長さ上限(64M等)を超えるようにならないか確認しておく
※SHOW VARIABLES LIKE '%max_allowed_packet%';

IN句の上限(Oracleは1000等)を超えるようにならないか確認しておく

順番

絞り込み後のレコード数が少なくなるものから指定する

JOIN or WHERE

WHERE句でテーブルの結合はしない
絞り込み条件とテーブルの結合は句を分ける

WHERE INで数値の列に対してCSVを使う

WHERE IN (1,2,3,4);
※TODO: prepared statementで検証する

NULL

= NULLではなく、IS NULL
NULLのデータがあった場合の挙動を確認しておく
INの場合は問題ない

COALESCE(カラム名, 0)等でNULLは変換する
※COALESCEの扱いは SELECT 句 (select.md) を参照

NOT IN

NOT INの場合は条件に当てはまるレコードが0になる
※NOT INの対象は基本的に主キーでNOT NULLにする

※col NOT IN (1, null)

※colが1の場合
※NOT (col = 1 OR col = null)
※NOT (TRUE OR NULL)
※NOT (TRUE)
※FALSE

※colが2の場合
※NOT (col = 1 OR col = null)
※NOT (FALSE OR NULL)
※NOT (NULL)
※NULL

※もしNOT NULLではないカラムに使う場合はWHERE NOT EXISTS
WHERE NOT EXISTS (
  SELECT
    1
  FROM
    table t2
  WHERE
    t1.col = t2.col
)

サブクエリで外側の文に依存する場合

外側の行ごとにサブクエリが実行されるので、パフォーマンスが悪いので使わない

EXISTS

EXISTSは外側のテーブルを参照する(相関サブクエリ)のでEXISTSではなく、WHEREを使う

2005-05-25以前の借りた人の名前

# EXISTS
SELECT
  c.first_name,
  c.last_name
FROM
  customer c
WHERE
  EXISTS (
    SELECT
      *
    FROM
      rental r
    WHERE
      r.customer_id = c.customer_id
      AND
      date(r.rental_date) < '2005-05-25'
  );

# WHERE
SELECT
  c.first_name,
  c.last_name
FROM
  customer c
WHERE
  customer_id IN (
    SELECT
      customer_id
    FROM
      rental
    WHERE
     date(rental_date) < '2005-05-25'
);

WHERE INとWHERE IN

SELECT
  *
FROM
  film f
WHERE
  film_id IN(
    SELECT
      fc.film_id
    FROM
      film_category fc
    WHERE fc.category_id IN(
      SELECT
        category_id
      FROM
        category
      WHERE
        name = 'Action'
      )
  );

アクション映画一覧

WHERE INとINNER JOIN

SELECT
  *
FROM
  film f
WHERE
  film_id IN(
    SELECT
      fc.film_id
    FROM
      film_category fc
      INNER JOIN
        category c ON fc.category_id = c.category_id
    WHERE
      name = 'Action'
  );

EXISTS

SELECT
  *
FROM
  film f
WHERE EXISTS(
    SELECT
      *
    FROM
      film_category fc
      INNER JOIN
        category c ON fc.category_id = c.category_id
    WHERE
      fc.film_id = f.film_id
      AND
      c.name = 'Action'
  );

関連

• アプリケーションセキュリティ — 条件を文字列で組み立てない。パラメータ化以外は確実な対策にならない
• インデックス — 書いた条件がインデックスに乗るかどうか