SQLTeam.com | Weblogs | Forums

Multiple where not in condition not working




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')

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


		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

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


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


The link isn't working for me.


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


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.

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

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


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


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


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