WHERE句内でCASE文を使うとき、THENには比較演算子は使えない (SQL Server)

jintaSQL

タイトルの通り。

SQL Serverでは、WHERE句内でCASE文を使用する際に、THEN句内で比較演算子を直接使用することはできません。比較演算子を使用するためには、CASE文の結果を一時的な列に格納し、その列をWHERE句で使用する必要があります。

ChatGPT

SQLのWHERE句内で条件分岐したいときがある。

通常下のように

SELECT * FROM TABLE_TEST
WHERE 
USER_ID = ?
AND AGE >
 CASE WHEN GENDER = 'M' THEN 30 ELSE 25 END

のような感じ書くことが多いだろう。

しかし、

GENDER = ‘M’のとき、さらに条件を絞りたいときもある。

そのときは CASEの中でCASEを使ったり、

SELECT * FROM TABLE_TEST
WHERE 
USER_ID = ?
AND AGE > 
   CASE WHEN ISNULL(GENDER,'') <> '' THEN
      CASE 
         WHEN GENDER = 'M' THEN 30 ELSE 25 
      END
    ELSE 1
   END
   
SELECT * FROM TABLE_TEST
WHERE 
USER_ID = ?
AND 
 (
 CASE WHEN ISNULL(GENDER,'') <> '' THEN
      CASE 
         WHEN AGE > 30 THEN 1 ELSE 0
      END
    ELSE 1
   END) = 1

CASE文が1つの条件式となるように、AGE>30が当てはまるときは１として、成立し、ヒットする。当てはまらないときはWHERE句内が0=1となるので、ヒットしない。