SQL NOT IN仍包含应排除的行

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.

但是,根据NOT IN列表,我仍然会获得应该排除的行。例如,即使tests.author = 4,也应该排除id为16的测试。但是它在查询中返回,我不想要。

The statement is created programmatically depending on the situation.

该语句是根据情况以编程方式创建的。

Is there a syntax mistake that I'm making?

我正在制作语法错误吗?

4 个解决方案

#1

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

看看SQL Server的运算符优先级。你会看到它并且具有比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:

SQL Server将读取:

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

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

并且为了响应您的查询,SQL Server可能会返回一辆缓慢的蓝色汽车。

#2

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.

你必须把所有或括号括起来。

#3

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)

#4

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,

Omair你错放了'''首先要清楚你要选择哪个作者。假设我们需要,作者有作者= 4或者其id包含在-999,343,197等中并且其删除状态= 0和ID不能在457,2409 ......等等。你做的是,

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

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

(作者= 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, ...) ) )

((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,

所以您可以使用适当的括号更改SQL,

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

SELECT * FROM测试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,170 ,171,2223,2535,2754))

#1