有关左外部联接和条件的SQL标准 [英] SQL Standard Regarding Left Outer Join and Where Conditions

问题描述

在基于放置过滤条件的位置的查询中，基于过滤条件的结果不同.我的问题是:

I am getting different results based on a filter condition in a query based on where I place the filter condition. My questions are:

• 是否存在技术差异 在这些查询之间?
• SQL标准中是否有任何内容 解释了不同 查询中的记录集?

• Is there a technical difference between these queries?
• Is there anything in the SQL standard that explains the different recordsets from the queries?

给出简化的情况:

--Table: Parent  Columns: ID, Name, Description
--Table: Child   Columns: ID, ParentID, Name, Description

--Query 1
SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description
FROM   Parent p
   LEFT OUTER JOIN Child c ON (p.ID = c.ParentID)
WHERE  c.ID IS NULL OR c.Description = 'FilterCondition'

--Query 2
SELECT p.ID, p.Name, p.Description, c.ID, c.Name, c.Description
FROM   Parent p
   LEFT OUTER JOIN Child c
   ON (p.ID = c.ParentID AND c.Description = 'FilterCondition')

我假设查询将返回相同的结果集，而当它们没有返回时，我感到很惊讶.我使用的是MS SQL2005，在实际查询中，查询1返回了约700行，查询2返回了约1100行，但我无法检测到返回行和排除行的模式.查询1中仍然有许多行，其中子行包含数据和NULL数据.我更喜欢查询2的样式(而且我认为它是最佳选择)，但我认为查询将返回相同的结果.

I assumed the queries would return the same resultsets and I was surprised when they didn't. I am using MS SQL2005 and in the actual queries, query 1 returned ~700 rows and query 2 returned ~1100 rows and I couldn't detect a pattern on which rows were returned and which rows were excluded. There were still many rows in query 1 with child rows with data and NULL data. I prefer the style of query 2 (and I think it is more optimal), but I thought the queries would return the same results.

编辑/摘要:

这里提供了一些很好的答案.我很难选择授予谁答案.我决定选择mdma，因为它是第一个答案，也是最清晰的答案.根据提供的答案，以下是我的摘要:

There were some great answers provided here. I had a hard time choosing to whom to award the answer. I decided to go with mdma since it was the first answer and one of the clearest. Based on the supplied answers, here is my summary:

可能的结果:

• A:没有孩子的父母
• B:有孩子的父母
• |-> B1:有孩子的父母，其中没有孩子匹配过滤器
• \-> B2:父母中有1个或更多符合过滤条件的孩子

• A: Parent with no children
• B: Parents with children
• |-> B1: Parents with children where no child matches the filter
• \-> B2: Parents with children where 1 or more match the filter

查询结果:

• 查询1返回(A，B2)
• 查询2返回(A，B1，B2)

由于左连接，查询2始终返回父级.在查询1中，WHERE子句是在左联接之后执行的，因此排除了没有子项与过滤器匹配的子项的父项(情况B1).

Query 2 always returns a parent because of the left join. In query 1, the WHERE clause is performed after the left join, so parents with children where none of the children match the filter are excluded (case B1).

注意:在情况B1中，仅返回父信息，在情况B2中，仅返回与过滤器匹配的父/子信息.

Note: only parent information is returned in case B1, and in case B2 only the parent/child information matching the filter is returned.

HLGEM提供了一个很好的链接:

HLGEM provided a good link:

http://wiki.lessthandot.com/index.php/WHERE_conditions_on_a_LEFT_JOIN

推荐答案

第一个查询将返回父级没有子级或某些子级符合过滤条件的情况.具体而言，将省略父母有一个孩子但不符合过滤条件的情况.

The first query will return cases where the parent has no children or where some of the children match the filter condition. Specificaly, cases where the parent has one child, but it doesn't match the filter condition will be omitted.

第二个查询将为所有父母返回一行.如果过滤条件不匹配，则将为所有c列返回NULL.这就是为什么您在查询2中获得更多行的原因-带有不符合过滤条件的子项的父项会输出NULL子值，在第一个查询中它们会被过滤掉.

The second query will return a row for all parents. If there is no match on filter condition, a NULL will be returned for all of c's columns. This is why you are getting more rows in query 2 - parents with children that don't match the filter condition are output with NULL child values, where in the first query they are filtered out.

这篇关于有关左外部联接和条件的SQL标准的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！