CODEWITHSUNDEEP
sqlsql-server
Growstrong
Growstrong

Reputation: 37

Where CLAUSE and NULL values

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?

Upvotes: 2

Views: 2485

Answers (5)

Janaka Neranjan
Janaka Neranjan

Reputation: 26

Try to do as this.

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

Upvotes: -2

Dev
Dev

Reputation: 367

You May Simply use ISNULL function in your query

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

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74700

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)

Upvotes: 1

Richardissimo
Richardissimo

Reputation: 5773

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'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522581

You may add NULL to the whitelist:

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

Upvotes: 3

Related Questions