左连接和内连接的区别...永远永远 [英] Left and Inner Join difference... once forever

问题描述

我知道这里已经创建了许多线程&在互联网上有关此主题的信息.但是，我真的无法就两个语句之间的区别得出最终的结论！我的意思是，尝试并尝试通过查询可以达到所需的所有结果，但是我真的无法完全控制刀具！

I know that many threads has been created here & on the internet about this topic. But I really can't get the final point on the difference between the two statements! I mean, trying and trying I can reach all the results I need with my queries, but I really don't have full control of the knife!

我认为自己是一个很好的程序员和一个很好的SQL-ista，对此我感到as愧...

I'm considering myself a very good programmer and a very good SQL-ista and I feel a little ashamed about this...

这是一个例子:

• 我有一个包含网站页面的表格("web_page")
• 具有类别(类别")的表.
• 一个类别可以包含一个或多个页面，反之亦然
• 一个类别可能根本不包含任何页面
• 页面在网站上是否可见

因此，如果我想显示所有类别及其页面，我的意思是这两个类别都包含页面，而没有页面，则必须执行以下操作:

So if I want to show all the categories and their pages, I mean both categories with pages and without, I have to do something like this:

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

因此，如果一个类别没有页面，则该类别的记录上将显示web_page_id为NULL.

So if a category has no pages, I'll see web_page_id NULL on the record of that category.

但如果我这样做:

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

我将只选择具有至少一个网页的类别...但是为什么?

I'll select only the categories that have at least one web_page... But WHY?

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

This was just an example... I'd like to understand once forever this difference!

谢谢.

推荐答案

要使查询按预期工作，请将条件放入ON子句:

To make your query to work as you intended, put the condition into the ON clause:

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

此功能起作用的原因是(对于大多数数据库，但不是全部)WHERE子句对连接后的行之后进行过滤.如果该联接未导致网页行联接(因为该类别没有网页)，则该网页的所有列均为null，并且将值(如"Y")与null为false，因此那些未连接的行将被过滤掉.

The reason this works is (with most databases, but not all) the WHERE clause filters the rows After they are joined. If the join doesn't result in a web page row joining (because the category had no web pages), then all the columns of web page will be null, and any comparison of a value (like "Y") to a null is false, so those non-joining rows will be filtered out.

但是，通过将条件移到ON子句中，条件会在进行连接时在中执行 ，因此，您仅 join 行是，但是如果没有这样的行，您将只获得左连接null网页.

However, by moving the condition into the ON clause, the condition is executed as the join is made, so that you only join rows that are active = "Y", but if there aren't any such rows, you'll just get the left join null web page.

此版本的查询实际上是在说:给我所有类别及其活动网页(如果有的话)"

This version of the query is really saying: "give me all categories and their active web pages (if any)"

请注意，我说的是大多数数据库" ...例如mysql足够聪明，可以理解您要执行的操作，并且如果在mysql上运行，查询将按预期工作.

Note that I said "most databases"... mysql for example is smart enough to understand what you are trying to do, and your query will work as you intended if run on mysql.

这篇关于左连接和内连接的区别...永远永远的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！