Use case in where clause

Question

I am trying to add case statement to where condition in a query. My query looks like below-

select * from table_name
where
if(id==1) then col1 = 0
else           col1 is null

As shown in above query I want to add col1 = 0 in where condition if id-==0 else I want col1 is null in where condition. I tried

select * from table_name
where 
case id =1 then (col1 = 0)
else col1 is null
end

But the syntax is incorrect. Is there any way to do this?

Why not `Where (id = 1 AND col1 =0) OR (id <> 1 AND col1 IS NULL)`? — Beltaine, Oct 27 '17 at 14:35

Yogesh Sharma · Answer 1 · 2017-12-12T06:49:23.483

3

SELECT *
FROM table_name
WHERE isnull(id, '')= CASE(id)
               WHEN 1
               THEN 0
               ELSE ''
           END;

edited Dec 12 '17 at 06:49

answered Oct 27 '17 at 14:36

Yogesh Sharma

49,870
5
26
52

1

equal comparison with NULL ( id = NULL ) i dont think it will work – Mudassir Hasan Oct 27 '17 at 14:42

score 0 · Answer 2 · answered Oct 27 '17 at 14:37

I am not sure of your requirement but you can try if below satisfies you

select * from table_name
where 
isnull(col1,'') = case when id = 1 then   0
else '' end

Also try

select * from table_name
where 
( col1 = 0 and id = 1) OR ( col1 is null and id<>1 )

score 0 · Answer 3 · answered Oct 27 '17 at 14:40

0

I think you are looking for a case when solution. Which can be used as below to fill in a specific column. You do need to select the other columns as well when you do this though.

select 
case when id = 1 then 0 else null end as col1
from table_name

answered Oct 27 '17 at 14:40

A. van Esveld

238
2
13

score 0 · Answer 4 · answered Oct 27 '17 at 15:58

0

I think this is what you're looking for:

select case when id = 1 then 0 else null end as col1, * from table_name

answered Oct 27 '17 at 15:58

Niranjan Rajawat

553
2
9

Use case in where clause

4 Answers4

Linked