SQL Server query include null in where clause

Question

I have the table in following format. I am trying to query this table to get all the records which are not PC and PT.

So my query should return ID 3,4,5,6,7 and should ignore 1 and 2

    CREATE TABLE #t (ID INT, V VARCHAR(16))

    INSERT  INTO #t
    VALUES  (1, 'PC'),
            (2, 'PT'),
            (3, NULL),
            (4, NULL),
            (5, NULL),
            (6, 'PS'),
            (7, 'PD');

drop table #t

The query that I am using is which returns only 6 and 7 and ignores null records in the result set .:

select 
    id, v 
from 
    #t 
where  
    v != 'PC' and V != PT

Please let me know what should I do in order to include null in my result set?

Just use NOT IN ('PC', 'PT'). It will return all the rows except PC and PT — Paresh J
– Paresh J, Commented Apr 21, 2015 at 4:56
@PareshJ - NOT IN ('PC','PT') will also exclude NULL records. — ughai
– ughai, Commented Apr 21, 2015 at 4:58

Sudharshan · Accepted Answer · 2015-04-21 05:43:06Z

5

Your query should check null condition as well.

Query -

select id , v from #t where  v not in ('PC' , 'PT') OR v is null

answered Apr 21, 2015 at 5:43

Sudharshan

3481 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

backtrack · Accepted Answer · 2015-04-21 05:06:19Z

0

select id , v from #t where  v!='PC' and V!=PT or OR V IS NULL

All good. please add the null condition too

or

select id , v from #t where  v not in ('PC' , 'PT') and v is null

use not in It will return all the rows except PC and PT

edited Apr 21, 2015 at 5:06

answered Apr 21, 2015 at 4:54

backtrack

8,1545 gold badges56 silver badges103 bronze badges

4 Comments

devloper Over a year ago

the first query works but not the second .Thanks for the feedback

ps_prakash02 Over a year ago

SELECT ID, V FROM #t WHERE ISNULL(V,'') NOT IN ('PC' , 'PT')

Dhaval Over a year ago

@ps_prakash02 your query is way better put it as answer or else i will put same aas answer

Bridge Over a year ago

The first query won't work - you've got two ORs, and mixing AND and OR in your where clause without the brackets means results will return the wrong results

Hitesh · Accepted Answer · 2015-04-21 05:31:45Z

0

Instead of using != for every condition, use a not in and for handling null values, use isnull

select 
  id, v 
from 
  #t 
where  
  isnull(v,'') not in ('PC', 'PT')

answered Apr 21, 2015 at 5:31

Hitesh

3,5068 gold badges43 silver badges61 bronze badges

Collectives™ on Stack Overflow

SQL Server query include null in where clause

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related