左与内连接差..。一次永远

Question

我知道这里已经创建了许多关于这个主题的线程&在互联网上。但对于这两种说法之间的区别，我实在搞不懂最后一点！我的意思是，尝试和尝试，我可以达到所有的结果，我需要我的问题，但我真的没有完全控制刀子！

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

下面是一个例子：

• 我有一张有网页的桌子("web_page")
• 有类别(“类别”)的表格。
• 类别可以包含一个或多个页面，但反之亦然。
• 类别可能根本不包含任何页面。
• 网页可以在网站中可见或不可见。

所以，如果我想展示所有的类别和它们的页面，我指的是有页面和没有页面的两个类别，我必须这样做：

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

因此，如果类别没有页面，我将在该类别的记录中看到web_page_id NULL。

但如果我这么做了

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

我只会选择至少有一个web_page的类别.但为什么？

这只是个例子..。我想永远理解一次这种差异！

谢谢。

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"的行，但是如果没有任何这样的行，则只会得到左侧的join null网页。

这个查询版本的意思是：“给我所有类别和它们的活动网页(如果有的话)”。

注意我说过“大多数数据库”..。例如，mysql非常聪明，能够理解您想要做什么，如果在mysql上运行，您的查询将按照您的意愿工作。

Answer 2

之所以会发生这种情况，是因为SQL正在分阶段处理：

1. FROM子句(所有联接都在这里)；
2. WHERE条款；
3. GROUP BY条款；
4. 窗口函数(但与MySQL无关)；
5. ORDER BY条款

因此，如果您想要在web_page.active='Y'上进行筛选，或者希望使用相同的条件加入，这一点非常重要。在前一种情况下，连接已经完成，您只需过滤掉结果，将OUTER连接转换为INNER连接。在后一种情况下，您将实现所需的结果，因为不匹配的行将导致对应列的NULL值。

Answer 3

试着在理解部分帮助你。

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

您可以通过这样一个WHERE子句来解决这个问题：

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

但这并不能完全保证结果是相同的，所以正确的方法是将这一标准保留在ON的JOIN条款中。