SQL NOT IN still includes rows that should be excluded

Question

I have the following statement to find rows that include certain values but exclude others:

SELECT * 
FROM tests 
WHERE author = 4  
OR id = -999 
OR id = 276 
OR id = 343 
OR id = 197 
OR id = 170 
OR id = 1058 
OR id = 1328 
OR id = 1417 
AND is_deleted = 0 
AND id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, 
               2457, 16, 25, 1639, 2224, 1804, 2308, 197, 461, 1442, 
               1594, 460, 1235, 1814, 2467, 168, 172, 170, 171, 2223, 2535, 2754)

However, I am still getting rows that should be exclude, as per the NOT IN list. For example, a test with the id, 16, should be excluded even though the tests.author = 4. But it is being returned in the query, which I don't want.

The statement is created programmatically depending on the situation.

Is there a syntax mistake that I'm making?

parentheses might make it easier to debug... – Mitch Wheat Oct 21 '13 at 13:19 — Mitch Wheat, Oct 21 '13 at 13:19

score 3 · Answer 1 · answered Oct 21 '13 at 13:30

Have a look at SQL Server's operator precedence. You'll see that and has a higher precedence than or.

Say that you're looking for a fast car that is red or blue. If you write:

where speed = 'fast' and color = 'green' or color = 'blue'

SQL Server will read:

where (speed = 'fast' and color = 'green') or color = 'blue'

And in response to your query, SQL Server could return a slow blue car.

score 0 · Answer 2 · answered Oct 21 '13 at 13:19

Change your query to this:

SELECT * 
FROM tests 
WHERE (author = 4  OR id = -999 OR id = 276 OR id = 343 OR id = 197 OR id = 170 OR id = 1058 OR id = 1328 OR id = 1417) 
      AND is_deleted = 0
      AND id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, 2457, 16, 25, 1639, 2224, 1804, 2308, 197, 461, 1442, 1594, 460, 1235, 1814, 2467, 168, 172, 170, 171, 2223, 2535, 2754)

you have to put all your or in parenthesis.

score 0 · Answer 3 · answered Oct 21 '13 at 13:19

Try this::

SELECT 
* 
FROM tests 
WHERE 
(author = 4  
OR 
id in (-999,276 ,343 ,197 ,170 ,1058 ,1328 ,1417) 
AND is_deleted = 0 )
AND id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, 2457, 16, 25, 1639, 2224, 1804, 2308, 197, 461, 1442, 1594, 460, 1235, 1814, 2467, 168, 172, 170, 171, 2223, 2535, 2754)

dbw · Answer 4 · 2013-10-21T13:46:18.190

Omair you are misplacing the '(' first of all just be clear that you want to select which author.
Suppose we need,
Authors having author = 4 or whose id is contained in -999, 343, 197 etc and whose deleted status = 0 and ID must not be in 457, 2409 ,...... etc.
What you did was,

author = 4 OR id = -999 OR id = 276 ...
AND is_deleted = 0
AND id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, ...)

This is interpreted according to operator precedence as

(author = 4 ) OR ( id = -999 OR id = 276 ...
AND is_deleted = 0
AND id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, ...)
)

Here, we just need to add proper '(' to separate our conditions as we need

((author = 4 ) OR ( id = -999 OR id = 276 ...)
AND (is_deleted = 0)
AND (id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, ...) ) )

So You can change SQL with proper brackets,

SELECT * FROM tests
WHERE
( (author = 4) OR id in (-999,276 ,343 ,197 ,170 ,1058 ,1328 ,1417) )
AND ( is_deleted = 0 )
AND ( id NOT IN (457, 2409, 173, 400, 167, 277, 163, 404, 2222, 24, 26, 2457, 16, 25, 1639, 2224, 1804, 2308, 197, 461, 1442, 1594, 460, 1235, 1814, 2467, 168, 172, 170, 171, 2223, 2535, 2754) )

SQL NOT IN still includes rows that should be excluded

4 Answers4