左连接和内连接的区别...一劳永逸

Question

我知道这个主题已经在这里和互联网上创建了很多线程。但我真的无法理解这两个语句之间的区别！我的意思是，尝试并尝试，我可以使用我的查询达到所有需要的结果，但我真的没完全掌握它！

我认为自己是一个非常好的程序员和SQL专家，我对此感到有点羞愧...

以下是一个例子：

- 我有一个包含网站页面 ("web_page") 的表 - 一个包含类别 ("category") 的表。 - 一个类别可以包含一个或多个页面，但反之则不然。 - 一个类别可能根本没有页面。 - 页面可以在网站上可见或不可见。

因此，如果我想显示所有类别及其页面，我的意思是既包括有页面也包括没有页面的类别，我必须做以下操作：

FROM category
LEFT JOIN web_page ON ( web_page.category_id = category.category_id AND web_page.active = "Y" )

如果一个分类没有页面，那么该类别的记录中会出现web_page_id为空的情况。

但是如果我执行以下操作：

FROM category
LEFT JOIN web_page ON ( web_page.category_id = category.category_id )
...
WHERE web_page.active = "Y"...

我将仅选择至少有一个网页的类别... 但为什么？

这只是一个例子... 我想要永久理解这个区别！

谢谢。

- tobia.zanarella

Answer 1

为了让您的查询按照预期工作，请将条件放入ON子句中：

FROM category
LEFT JOIN web_page ON web_page.category_id = category 
   and web_page.active = "Y"

这个方法有效的原因是（对于大多数数据库，但不是所有），WHERE子句过滤连接后的行。如果连接没有导致网页行的连接（因为类别没有网页），那么网页的所有列都将是null，并且任何值（如"Y"）与null的比较都是错误的，所以这些非连接行将被过滤掉。

然而，通过将条件移动到ON子句中，条件在连接时执行，这样你只会连接active = "Y"的行，但如果没有这样的行，你将只得到左连接的null网页。

这个查询的版本实际上是在说：“给我所有的类别和它们的活动网页（如果有的话）”。

请注意，我说的是“大多数数据库”...例如mysql就足够聪明，可以理解你想做什么，如果在mysql上运行，你的查询将按照你的意图工作。

- Bohemian

---

给这个答案点个赞。谢谢。我一直想知道JOIN和WHERE哪个先执行。 - Jonas T

Answer 2

这是因为 SQL 是经过多个阶段处理的：

1. FROM 子句（所有联接都在此处）；
2. WHERE 子句；
3. GROUP BY 子句；
4. 窗口函数（虽然与 MySQL 无关）；
5. ORDER BY 子句。

所以如果你想要在 web_page.active='Y' 上进行 筛选 或者想要使用相同的条件进行 连接，那么这非常重要。在前一种情况下，连接已完成，您只需过滤结果，将您的 OUTER 连接转换为 INNER 连接。在后一种情况下，您将获得所需的结果，因为不匹配的行将导致相应列的 NULL 值。

- vyegorov

Answer 3

试图帮助您理解。

在 SQL 语言中，Consider 是一个方面，当在 LEFT JOIN 中指定条件时，它适用于查找要匹配的记录。当在底部的 WHERE 子句中指定条件时，它适用于所有记录 - 在连接发生后。这会产生一个意外的副作用，将 LEFT JOIN 更改为 INNER JOIN，正如您所看到的。

您可以通过使用以下方式的 WHERE 子句来解决这个问题：

WHERE COALESCE(web_page.active,"Y") = "Y"

但这并不能保证得到相同的结果，因此正确的做法是将该条件保留在 JOIN 的 ON 子句中。

- Andrew