CODEWITHSUNDEEP
mysqlsqlnotin
Omair Vaiyani
Omair Vaiyani

Reputation: 552

SQL NOT IN still includes rows that should be excluded

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?

Upvotes: 2

Views: 78

Answers (4)

Deepak Bhatia
Deepak Bhatia

Reputation: 6276

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

Upvotes: 0

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 3

Sashi Kant
Sashi Kant

Reputation: 13465

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)

Upvotes: 0

gzaxx
gzaxx

Reputation: 17600

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.

Upvotes: 0

Related Questions