Case when condition is Where clause SQL Server 2017-CodePudding

I'm trying to a use a CASE expression in the WHERE clause in SQL. I don't know how I to put it in.

For example:

select id , name = case when first_name = 'a' then 'NA' else null end
from MyTable 
where case when first_name = 'a' then 'NA' else null end is null

I want to show results when they are null. How can I do that please?

CodePudding user response：

Based on your query here is query that should work.

SELECT 
    [id] 
    ,CASE [first_name] WHEN 'a' THEN 'NA' ELSE NULL END AS 'name' 
FROM [MyTabl] 
WHERE 1 = CASE [first_name] WHEN 'a' THEN 0 ELSE 1 END

You can use the case to return a string or numeric and then just say where equal to that value. Warning this can be quite slow and it is better to use an where statement.
This would be better as a select statement :

SELECT 
    [id] 
    ,CASE [first_name] WHEN 'a' THEN 'NA' ELSE NULL END AS 'name' 
FROM [MyTabl] 
WHERE [first_name] <> 'a'

Alternative query :

SELECT 
    [id] 
    ,CASE [first_name] WHEN 'a' THEN 'NA' ELSE NULL END AS 'name' 
FROM [MyTabl] 
WHERE [id] NOT IN ( SELECT [id] WHERE [first_name] = 'a' )