Where CLAUSE and NULL values

user_10378693 0
Viewed 1194

I have following columns

+---------+----------+----------+
| Account | Employee |   Org    |
+---------+----------+----------+
|     123 | ABC      | Gold     |
|     234 | PQR      | Silver   |
|     456 | XYZ      |          |
|     567 | MNQ      | Gold     |
|     714 | RPT      | Platinum |
|     819 | MKQ      | BRONZE   |
|     100 | LPA      | Tin      |
|     101 | QRT      |          |
|     104 | LMY      | Platinum |
+---------+----------+----------+

Now the intention is to filter out all employees who have ORG like Bronze or Tin

Straight forward this is with query;

Select * from table 1 where Org NOT IN ('Bronze','Tin')

However this will filter records which have ORG as NULL which i don't want.
So basically above query gives me;

+---------+----------+----------+
| Account | Employee |   Org    |
+---------+----------+----------+
|     123 | ABC      | Gold     |
|     234 | PQR      | Silver   |
|     567 | MNQ      | Gold     |
|     714 | RPT      | Platinum |
|     104 | LMY      | Platinum |
+---------+----------+----------+

Where as i want;

+---------+----------+----------+
| Account | Employee |   Org    |
+---------+----------+----------+
|     123 | ABC      | Gold     |
|     234 | PQR      | Silver   |
|     456 | XYZ      |          |
|     567 | MNQ      | Gold     |
|     714 | RPT      | Platinum |
|     101 | QRT      |          |
|     104 | LMY      | Platinum |
+---------+----------+----------+

I could get this result by replacing NULL with some values with case statement and then filter records but i think there must be more clean and easy way of doing this?

My Current Query;

Select * from (
 select ACCOUNT , EMPLOYEE , 
       CASE WHEN ORG IS NULL THEN 'RETAIN'
       ELSE ORG END AS EORG FROM table1 ) P
       WHERE P.EORG not in ('Bronze','Tin')

Is there better way?

sql-server sql
5 Answers

You may add NULL to the whitelist:

SELECT *
FROM table1
WHERE Org NOT IN ('Bronze', 'Tin') OR Org IS NULL;
user_18632290

Anything that is null will return false when compared to anything else, even another null. The result of false survives all the way out to the top level; you can't turn it around with NOT

This is false:

'X' = null

This is also false:

NOT ('X' = null)

And this is false:

 null IN ('X', null)

This too:

null NOT IN('x', null)

It may seem counterintuitive at first but these are the rules. You can only ever specifically test whether something is or isn't null with 

something IS NULL
something IS NOT NULL

So either cater for nulls specifically or convert them to something real before you compare:

Select * from table where column not in (...) or column is null

Select * from table where coalesce(column, 'x') not in (...)

For the second form to work, whatever you put in the coalesce (I put x) should not appear in the IN list (otherwise it IS IN and will hence be filtered out), nor should it ever appear in the data. The first form would be preferred as not all databases will successfully use an index on the column if coalesce is used

Side point, when posting on stack overflow please prefix your code lines with 4 blank spaces - it will then show in a grey box in a mono space font and multiple spaces are preserved (you've used quotes, yellow box, no monospace, can make things harder to read)

user_14106640

This is due to the way IN operator works. On that page is a large caution...

Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results.

Try this..

WHERE P.EORG <> 'Bronze' and P.EORG <> 'Tin'
user_51981400

You May Simply use ISNULL function in your query

SELECT * FROM table1 WHERE ISNULL(Org,'') NOT IN ('Bronze', 'Tin')

user_70465040

Try to do as this.

SELECT * 
FROM   [dbo].[stockmaster] 
WHERE  openingweight NOT IN ( '10' ) OR openingweight IS NULL
user_92867560
Related