mysql - mySQL:如何选择FROM表WHERE IN LIST而不是另一个列表

是否可以从一个表中选择值，其中的值不存在于一个列表中，但确实存在于另一个列表中…或者是相反？
例如。

SELECT COUNT(g.`property`) as `number`, g.`property`
  FROM `foo` g
 WHERE `theID` IS IN (SELECT `theID`
                        FROM `tableofIDS`
                       WHERE `theID` = '54252')
          AND NOT IN (SELECT `theID`
                        FROM `anotherTableofIDS`
                       WHERE `theID` = '54252')

最佳答案

SELECT COUNT(g.`property`) as `number`, g.`property`
FROM `foo` g
WHERE `theID` IN (SELECT `theID` FROM `tableofIDS` WHERE `theID` = '54252')
  AND `theID` NOT IN (SELECT `theID` FROM `anotherTableofIDS` WHERE `theID` = '54252')
GROUP BY g.`property`

或者，您可以使用连接，这样可以更好地执行以下操作：

SELECT COUNT(g.`property`) as `number`, g.`property`
FROM `foo` g JOIN (
    SELECT `theID`
    FROM `tableofIDS`
    WHERE `theID` = '54252'
   ) id1 ON g.theID = id1.theID
  LEFT JOIN (
    SELECT `theID`
    FROM `anotherTableofIDS`
    WHERE `theID` = '54252'
  ) id2 ON g.theID = id2.theID
WHERE id2.theID IS NULL
GROUP BY g.`property`