CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Alex
Alex

Reputation: 23

SQL where clause with multiple 'not in' checks

This looks like it should be a relatively simple query but can someone please explain why the second query doesn't return rows 2 and 3 but the third one does? Basically, how can I make a query satisfy both 'not in' clauses? 

declare @t table (id int identity, code1 char(2), code2 char(2))

insert into @t (code1, code2) values ('AA','BB')
insert into @t (code1, code2) values ('AA','CC')
insert into @t (code1, code2) values ('DD','EE')

select * from @t where code1 = 'AA' and code2 = 'BB'
select * from @t where (code1 != 'AA' and code2 != 'BB')
select * from @t t1 left join @t t2 on t2.id = t1.id and t2.code1 = 'AA' and t2.code2 = 'BB' where t2.id is null

* updated *

Thanks for the answers. I was hoping that the "(code1 != 'AA' and code2 != 'BB')" would all need to evaluate, ie the row 1 (AA,BB) would be filtered out but rwo 2 (AA,CC) would be returned.

Is there no simple way to stipulate that two constraints need to be met, that was what I was hoping for by using brackets and the and statement??

Upvotes: 2

Views: 4810

Answers (6)

Mark Bosley
Mark Bosley

Reputation: 23

As a general principle: Fields from a LEFT JOINED table should not show up in the WHERE clause.

SELECT * FROM CUSTOMER
LEFT OUTER JOIN
ORDERS 
    ON CUSTOMER.CustomerID = ORDERS.CustomerID
WHERE ORDERS.CustomerID <> 1208

The WHERE clause turns this into an INNER JOIN. If you want to exclude 1208, check the ORDERS.CustomerID.

Upvotes: 0

codeulike
codeulike

Reputation: 23064

To make the second query equivalent to the third query you need:

select * from @t where (code1 != 'AA' or code2 != 'BB')

draw a logic table to figure it out:

Row    p1 (code1 != 'AA')   p2 (code2 != 'BB')   p1 AND p2    p1 OR p2
---    ------------------   ------------------   ---------    --------
 1         false                false              false        false
 2         false                true               false        true
 3         true                 true               true         true

Edit: In response to your update: AND-ing two NOTS together can be counter intuitive (again, draw yourself a logic table if you need to see how it works). A clearer way of using NOT is to move the 'NOT' outside the bracket as Joe suggested:

select * from @t where NOT (code1 = 'AA' or code2 = 'BB')

Upvotes: 1

Leonardo
Leonardo

Reputation: 11401

actually query 2 should only return row 3 because 'DD' != 'AA' AND 'EE' != 'BB'

Upvotes: 1

Roopesh Shenoy
Roopesh Shenoy

Reputation: 3447

The third query is a left join - and basically no condition for t1 so all the rows for t1 will be returned, even if there is some condition on t2 (rows in t2 will be filtered out by the conditions put, but since it is a left join, it does not affect rows returned by t1)

Upvotes: 0

Orbling
Orbling

Reputation: 20612

The second query can not return record 2, because it says "code1 != AA" - this is not true for the second record, so it should just return record 3.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

For query 2, code1 != 'AA' is FALSE for your second row.

Upvotes: 2

Related Questions