I have a column which can contain values from 1 to 6 and null..
Then when I try to run the query below.. rows with null value on that column do not return
select * from customer where Position not IN ('1','2', '3', '4' ,'5', '6')
Is there way I can retrieve null results without adding this on above query
OR Position is null
One trick you could use would be to coalesce the position to some value which does not appear in the restricted list, e.g.
SELECT *
FROM customer
WHERE COALESCE(Position, '9') NOT IN ('1', '2', '3', '4' ,'5', '6')
However, I would probably just use an is null clause here:
SELECT *
FROM customer
WHERE Position NOT IN ('1', '2', '3', '4' ,'5', '6') OR
Position IS NULL
Is there way I can retrieve null results without adding
OR Position is null
There are plenty of ways, but OR Position is null is the easiest, the shorteste and the most natural way.
If you don't like it, then there are few ideas below:
select *
from customer where ID NOT IN (
select ID
from customer where Position IN ('1','2', '3', '4' ,'5', '6')
);
select *
from customer where
1 = CASE WHEN Position IN ('1','2', '3', '4' ,'5', '6')
THEN 0 ELSE 1 END;
select * from customer c
left join (
SELECT * FROM unnest(ARRAY[1,2,3,4,5,6]) x
) x
ON c.position = x.x
WHERE x.x IS NULL
If you want to return the NULL result then include that as a condition in your WHERE clause else NO it's not possible
select * from customer
where Position not IN ('1','2', '3', '4' ,'5', '6')
or Position is null
You can add a condition : Position IS NULL
SELECT *
FROM Customer
WHERE Position NOT IN ('1','2', '3', '4' ,'5', '6') OR Position IS NULL
You can see this here => http://rextester.com/HEZ87754
Hope this helps!!!
