mySQL：如何从列表中的表中选择而不是在另一个列表中

Question

是否可以从表中选择值，这些值不存在于一个列表中，但确实存在于另一个列表中......或者反之亦然？

例如

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

Answer 1

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`

Answer 2

没有仔细思考，但发现了两个语法错误。尝试

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

编辑：将 NOT IN 更改为 NOT EXISTS

Answer 3

我想也许你正在寻找的是这样的：

    SELECT COUNT(g.property) as `number`,
           g.property
      FROM foo g
      JOIN tableofIDs i USING (theID)
RIGHT JOIN anothertableofIDs x USING (theID)
     WHERE g.theID = '54252'
       AND x.theID IS NULL
  GROUP BY g.property