mysql - INTERSECT 优于子查询吗？

Question

我正在做题

Find all students who do not appear in the Likes table (as a student who likes or is liked) and return their names and grades. Sort by grade, then by name within each grade.

我建议执行以下操作，获取所有不喜欢的人并将这些人与不喜欢任何人的人相交:

SELECT name, grade
  FROM Highschooler h1
LEFT JOIN Likes l1
  ON (l1.ID1 = h1.ID)
WHERE l1.ID1 IS NULL
INTERSECT
SELECT name, grade
  FROM Highschooler h1
LEFT JOIN Likes l1
  ON (l1.ID2 = h1.ID)
WHERE l1.ID2 IS NULL
ORDER BY grade, name

另一种方法是使用子查询(我在网上找到的)

select name, grade from Highschooler H1
where H1.ID not in (select ID1 from Likes union select ID2 from Likes) 
order by grade, name;

首选哪种方式？我认为我的方法更具可读性。

Answer 1

正如 Adrien 所说，这两种方式都是可以接受的，我认为您的方式更具可读性，因为大写的函数和更好的缩进，而不是因为查询本身。

例如，我会这样做:

SELECT name, grade
FROM Highschooler H1
LEFT JOIN Likes AS L1 ON L1.ID1 = H1.ID OR L1.ID2 = H1.ID
WHERE L1.ID1 IS NULL
ORDER BY grade, name

这种情况称为反加入。在此处阅读更多信息

https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

这是另一篇关于不同类型联接的不错的博客文章，并附有一些图表: http://blog.jooq.org/2015/10/06/you-probably-dont-use-sql-intersect-or-except-often-enough/

我们的情况是