NOT IN AND IN condition mysql

Question

我有SQL查询设计如下。

SELECT (THIS ITEMS) 
FROM (TABLES) 
WHERE(SOME CONDITION) AND NOT IN (CONDITION) AND IN (CONDITION)

但是没有按照要求获得正确的输出，因为“IN”条件没有正确工作

Answer 1

您需要一个要比较的列。正确的语法是(请注意column_expression)：

SELECT <select_list>
FROM <table_expression>
WHERE <column_expression> [NOT] IN (<comparison_list>)

例如,

SELECT col1, col2
FROM tab
WHERE col3 IN ('yes', 'no')
AND col4 NOT IN ('no', 'maybe')

注您也可以使用单列结果集而不是静态比较列表，如下所示：

SELECT col1, col2
FROM tab
WHERE col3 IN (SELECT filterValues FROM table2)

但是在这种情况下使用EXISTS子句要好得多：

SELECT col1, col2
FROM tab
WHERE EXISTS (SELECT 1 FROM table2 WHERE filterValues = col3)

Answer 2

在某些情况下，您可能需要使用括号。

Example 1:
Select all persons who are active and have an invoice 
Person->Invoice (1 to Many)
SELECT * FROM person WHERE active=1 AND person_id IN (SELECT person_id FROM invoice)

Example 2:
Select all person who are active, do not have a cat, and have an invoice

SELECT * FROM person WHERE active=1 AND person_id NOT IN (SELECT person_id FROM people_with_cats) AND person_id IN  (SELECT person_id FROM invoice)