Multiple where not in condition not working

Transact-SQL
, ,
1

Hi,

I am working on my query to use Not In condition in my where clause but give me different result. below is a sample DDL and Desired result. thank you.

declare @s1 table
(line nvarchar(10),
location nvarchar(10)
)

INSERT	@s1 VALUES('L3A','Bad')
INSERT	@s1 VALUES('L3B','Bad')
INSERT	@s1 VALUES('L3','Bad')
INSERT	@s1 VALUES('L1','good')
INSERT	@s1 VALUES('L2B','good')
INSERT	@s1 VALUES('L3','good')
INSERT	@s1 VALUES('L6','good')
INSERT	@s1 VALUES('L6A','good')
INSERT	@s1 VALUES('L3TR','bad')
INSERT	@s1 VALUES('L3TR','bad')
INSERT	@s1 VALUES('L3TR','good')
INSERT	@s1 VALUES('L3TR','good')
INSERT	@s1 VALUES('L3TR','good')

select 
		line, location
from @s1
where line not in ('L3A','L3B')
and (line in ('L3TR') and location not in ('Bad'))

or 

select 
		line, location
from @s1
where line not in ('L3A','L3B')
and (line ='L3TR' and location='Good')


expected result.

line---	location
----------------
L3---	Bad
L1---	good
L2B---	good
L3---	good
L6---	good
L6A---	good
L3TR---	good
L3TR---	good
L3TR---	good
2 
select 
		line, location
from @s1
where line not in ('L3A','L3B')
and (line <> 'L3TR' or location not in ('Bad'))
1 Like
3

You might need to read the following as NULLs may not be behaving the way you expect:

4

The link isn't working for me.

5

Hmm, I don't see anything related to NULLs or OUTER joins in this q.

6

I may be missing something but personally I would change the "not in ('bad')" for a "<> 'bad' ".

I think an inequality operator would be more efficient than the not in especially as you are only specifying one value in the second not in clause.

1 Like
7 
select
		line, location
from @s1
where
    location = 'Good'     -- location <> 'Bad' 
    and line not in ('L3A', 'L3B')

I'm assuming:
You only want 'Good'.
You don't want L3A or L3B.

1 Like
8

But SQL converts the NOT IN to <>(s) anyway, right? Likewise, IN ('A', 'B', 'C') gets converted to = 'A' or = 'B' or = 'C'.

9

True but why make the server do more work :wink:

10

Because I'd rather the machine do grunt work like that instead of me :grin:. That's why I bought the friggin' thing!