postgreOpposite of UNION SQL Query

Question

>我有 2 个表：

interests (storing the interest ID and name)
person_interests(storing the person_id and interest_id)

如何选择特定人员未选择的所有兴趣？

我尝试了以下SQL查询，但仍然没有得到预期的结果

SELECT * 
  FROM interests LEFT JOIN person_interests 
               ON interests.id=person_interests.person_id
 WHERE person_interests.id IS NULL 
   AND person_id=66;

Answer 1

使用 NOT EXISTS

SELECT *
FROM interests
WHERE NOT EXISTS (
        SELECT person_interests.interest_id
        FROM person_interests
        WHERE person_id = 66
            AND interests.id = person_interests.interest_id
        )

Answer 2

SELECT * from interests  
WHERE interest_id NOT IN  
(SELECT interest_id FROM person_interests WHERE person_id=66)

Answer 3

有几件事正在发生。

首先，我认为您的连接有错误。不应该是interests.id=person_interests.interest_id而不是interests.id=person_interests.person_id吗？

除此之外，我仍然不认为你会得到想要的结果，因为你的person_id过滤器位于左外连接的右侧，从而将其变回内部联接。有几种方法可以解决这个问题。以下是我可能会做的：

SELECT * 
FROM 
  (SELECT interests.*, person_id 
  FROM interests LEFT JOIN person_interests 
       ON interests.id=person_interests.interest_id
   WHERE person_interests.id IS NULL )
WHERE person_id=66;