誤解しやすいSQL

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

プログラムコードを書く際、書く言語の文法を理解している事は重要であるが、

理解している様で実は意外と誤解しているエンジニアが(比較的)多そうな、SQLのWHERE条件について、挙動と注意点を記述する。

■ 前提

次の様なデータを持つテーブル「t」があるとする。

+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 | NULL | NULL |
|  2 | NULL | 2222 |
|  3 | 1111 | 2222 |
|  4 | 1111 | 1111 |
+----+------+------+

このテーブルに対して、WHERE句で条件を指定して抽出するSELECT文を考える。

SELECT id FROM t WHERE /* ここに条件を記載 */;

このWHERE句が以下のパターン1-4に記述する様な内容であった場合、どのidが抽出されるか正確に想像できるであろうか。

余談

Oracle, MySQL 等、DUALが使用可能なデータベースでは、次の様に記述すれば簡単に確認ができる。

SELECT * FROM (
SELECT 1 id, NULL a, NULL b FROM DUAL UNION 
SELECT 2 id, NULL a, 2222 b FROM DUAL UNION 
SELECT 3 id, 1111 a, 2222 b FROM DUAL UNION 
SELECT 4 id, 1111 a, 1111 b FROM DUAL
) t
WHERE /* ここに条件を記載 */;

■ パターン1 (通常比較)

問題

(1) a = b
(2) a <> b
(3) a < b
(4) a > b
(5) a IS NULL
(6) a IS NOT NULL

結果

(1) 4
(2) 3
(3) 3
(4) なし
(5) 1, 2
(6) 3, 4

結果から言える注意点

=, >, <>, <= を指定した場合、NULLデータはヒットしなくなる。

■ パターン2 (パターン1にNOT付加)

問題

(1) NOT a = b
(2) NOT a <> b
(3) NOT a < b
(4) NOT a > b
(5) NOT a IS NULL
(6) NOT a IS NOT NULL

結果

(1) 3
(2) 4
(3) 4
(4) 3, 4
(5) 3, 4
(6) 1, 2

結果から言える注意点

NOTを付けると付けなかった場合に抽出されなかったレコードが抽出されると考えがちであるが、実際にはそうではない。

中身の比較演算子(IS NULL等も含めるとする)が逆の意味を持つ比較演算子に変化したと考えると理解し易い。

= の逆は <>
> の逆は <=
< の逆は >=
IS NULL の逆は IS NOT NULL

※即ち以下と同じになる。

(1) a <> b
(2) a = b
(3) a >= b
(4) a <= b
(5) a IS NOT NULL
(6) a IS NULL

■ パターン3 (複数条件)

問題

(1) a = b AND a IS NOT NULL
(2) a = b AND b IS NOT NULL
(3) a = b OR b IS NULL
(4) a < 1111 AND b < 2222 OR a < b

結果

(1) 4
(2) 4
(3) 1, 4
(4) 3

結果から解る点

パターン1で述べた様に、=を用いるとNULLデータはヒットしなくなる(もともとIS NOT NULL を含意している)ので、「AND a IS NOT NULL」等を付け足しても結果に差は出ない。

■ パターン4 (パターン3にNOT付加)

問題

(1) NOT (a = b AND a IS NOT NULL)
(2) NOT (a = b AND b IS NOT NULL)
(3) NOT (a = b OR b IS NULL)
(4) NOT (a < 1111 AND b < 2222 OR a < b)

結果

(1) 1, 2, 3
(2) 1, 3
(3) 3
(4) 4

結果から言える注意点

この結果からも解る様に、NOTを付けなかった場合に抽出されなかったレコードがNOTを付けると抽出される訳ではない((1)と(2)はパターン3では同じ結果であるがパターン4は結果が異なる)。

次の様に考えると理解し易い。

ANDやORを含む句にNOTを付けると、それぞれの比較演算子は逆の意味を持つ演算子になり、ANDはORに、ORはANDに変化する。また、ANDとORが複合している場合は、もともとANDであった条件同士の方が結合度が強くなる。

※即ち以下と同じになる。

(1) a <> b OR a IS NULL
(2) a <> b OR b IS NULL
(3) a <> b AND b IS NOT NULL
(4) (a >= 1111 OR b >= 2222) AND a >= b

■ 結論

NULL値が含まれるデータに対するWHERE条件は、数学的な式の結果と異なるので、誤解をし易い。特に、NOTが含まれる条件は、解釈の際に1手間増えるというだけでなく、誤解する確率を上げる事になるので、もし同じ条件でNOTを使わない記述が可能な場合は、理由がなければ使わない方が良いと言えるであろう。