SQL Server / T-SQL：NOT IN句とNULL値の落とし穴

2024-04-05

SQLにおけるNOT IN句とNULL値：詳細解説

NOT IN句は、指定された値リストに一致しないレコードを取得するのに役立ちます。しかし、NULL値との関わりにおいては、いくつかの注意点が存在します。本記事では、SQL、SQL Server、T-SQLにおけるNOT IN句とNULL値の相互作用について、詳細かつ分かりやすく解説します。

NOT IN句の基礎
NULL値とNOT IN句の動作
CASE式とCOALESCE関数を用いた対処方法

NOT IN句は、指定された値リストに一致しないレコードを検索するために使用されます。

例：

SELECT * FROM テーブル名
WHERE 列名 NOT IN ('値1', '値2', ...);

この例では、列名が値1、値2、... いずれにも一致しないレコードがすべて選択されます。

NULL値は、NOT IN句の動作に影響を与える可能性があります。

1 NULL値とIN句

NULL値を含む列をIN句で比較する場合、以下の点が重要です。

NULL値は、どの値とも一致しません。
NULL値を含むレコードは、IN句で指定された値リストに存在しないものとして扱われます。

SELECT * FROM テーブル名
WHERE 列名 IN ('値1', '値2', NULL);

この例では、列名がNULLであるレコードは選択されません。

結果として、NULL値を含むレコードは、NOT IN句の評価結果に影響を与えません。

SELECT * FROM テーブル名
WHERE 列名 NOT IN ('値1', '値2', NULL);

NULL値とNOT IN句を組み合わせる場合、以下の比較演算子を使用できます。

IS NULL：列名がNULLかどうかを検査します。

SELECT * FROM テーブル名
WHERE 列名 IS NOT NULL AND 列名 NOT IN ('値1', '値2');

NULL値とNOT IN句の複雑な処理には、CASE式やCOALESCE関数を用いることで、より柔軟な対応が可能になります。

1 CASE式

CASE式を使用することで、NULL値を別の値に変換してからNOT IN句で比較することができます。

SELECT * FROM テーブル名
WHERE CASE WHEN 列名 IS NULL THEN '' ELSE 列名 END NOT IN ('値1', '値2');

この例では、列名がNULLの場合、空文字("")に変換してからNOT IN句で比較します。

SELECT * FROM テーブル名
WHERE COALESCE(列名, '') NOT IN ('値1', '値2');

この例では、列名がNULLの場合

環境:

SQL Server 2019

コード:

-- テーブル作成
CREATE TABLE テーブル名 (
    列名 VARCHAR(50)
);

-- データ挿入
INSERT INTO テーブル名 (列名) VALUES ('値1'), ('値2'), (NULL);

-- NOT IN 句による検索
SELECT * FROM テーブル名
WHERE 列名 NOT IN ('値1', '値2');

-- IS NULL と NOT IN 句による検索
SELECT * FROM テーブル名
WHERE 列名 IS NOT NULL AND 列名 NOT IN ('値1', '値2');

-- CASE 式による NULL 値の変換
SELECT * FROM テーブル名
WHERE CASE WHEN 列名 IS NULL THEN '' ELSE 列名 END NOT IN ('値1', '値2');

-- COALESCE 関数による NULL 値の置換
SELECT * FROM テーブル名
WHERE COALESCE(列名, '') NOT IN ('値1', '値2');

実行結果:

-- 1行目のレコードのみ選択
列名
-------
値1

-- 1行目と2行目のレコードのみ選択
列名
-------
値1
値2

-- 1行目と2行目のレコードのみ選択
列名
-------
値1
値2

-- 1行目と2行目のレコードのみ選択
列名
-------
値1
値2

最初に、テーブル名というテーブルを作成し、列名という列を定義します。
次に、値1、値2、NULLという値を列名に挿入します。
その後、NOT IN句、IS NULLとNOT IN句、CASE式、COALESCE関数を使用して、NULL値とNOT IN句の動作を検証します。

補足:

上記のサンプルコードは、SQL Server 2019で動作確認しています。他のデータベースを使用する場合は、構文を調整する必要があります。
NULL値の扱いに関しては、データベースによって異なる場合があります。詳細は、使用しているデータベースのマニュアルを参照してください。

NOT IN句とNULL値を比較する他の方法

EXISTSサブクエリを使用することで、NULL値を含むレコードをより柔軟に処理することができます。

SELECT * FROM テーブル名
WHERE EXISTS (
    SELECT 1 FROM テーブル名
    WHERE 列名 = 列名
    AND 列名 NOT IN ('値1', '値2')
);

SELECT t1.*
FROM テーブル名 AS t1
LEFT JOIN テーブル名 AS t2 ON t1.列名 = t2.列名
WHERE t2.列名 IS NULL
AND t2.列名 NOT IN ('値1', '値2');

仮想テーブルを使用することで、NOT IN句とNULL値の比較をより分かりやすく記述することができます。

CREATE TABLE #仮想テーブル (
    列名 VARCHAR(50)
);

INSERT INTO #仮想テーブル (列名) VALUES ('値1'), ('値2');

SELECT * FROM テーブル名
WHERE 列名 NOT IN (SELECT 列名 FROM #仮想テーブル);

DROP TABLE #仮想テーブル;