ON和WHERE中的SQL外部联接筛选条件 [英] SQL Outer Join Filtering Conditions in ON versus WHERE
问题描述
以下查询为何不同?我想加入LEFT OUTER，但需要用条件过滤子项.我以为这些查询本质上是相同的(只是语法不同)，但是如果将条件放在 ON 与 WHERE :
Why are the following queries different? I want a LEFT OUTER join, but need to filter the children with a condition. I thought these queries were essentially the same (just different syntax), but I get different results if I put the condition in ON versus WHERE:
-- Query 1: Filter in WHERE
SELECT p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM @Parent p
LEFT OUTER JOIN @Child c
ON (p.ID = c.ParentID)
WHERE c.ID IS NULL OR c.Name = 'T';
-- Query 2: Filter in ON
SELECT p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM @Parent p
LEFT OUTER JOIN @Child c
ON (p.ID = c.ParentID AND c.Name = 'T');
我从 Query 2 开始，但是它显示了结果中的所有父项，而不是具有匹配子项的子集，所以我切换到 Query 1 .这是一个示例:
I started with Query 2 but it showed all of the parents in the results, not the subset with matching children, so I switched to Query 1. Here is an example:
DECLARE @Parent TABLE (
ID int IDENTITY(1, 1) PRIMARY KEY
, Name nvarchar(40) NOT NULL
);
DECLARE @Child TABLE (
ID int IDENTITY(1, 1) PRIMARY KEY
, Name nvarchar(40) NOT NULL
, ParentID int NULL
);
-- Parents
INSERT @Parent (Name)
VALUES ('A'), ('B'), ('C'), ('D')
;
-- Children: permutations to parents.
-- NOTE: 'D' has no children
INSERT @Child (Name, ParentID)
VALUES ('T', 1)
, ('U', 2)
, ('V', 1), ('V', 2)
, ('W', 3)
, ('X', 1), ('X', 3)
, ('Y', 2), ('Y', 3)
, ('Z', 1), ('Z', 2), ('Z', 3)
;
-- Query 1: Filter in WHERE
SELECT p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM @Parent p
LEFT OUTER JOIN @Child c
ON (p.ID = c.ParentID)
WHERE c.ID IS NULL OR c.Name = 'T';
-- Query 2: Filter in ON
SELECT p.ID, p.Name, c.ID, c.Name, c.ParentID
FROM @Parent p
LEFT OUTER JOIN @Child c
ON (p.ID = c.ParentID AND c.Name = 'T');
查询1:结果
| ID | 名称 | ID | 名称 | ParentID |
|---|---|---|---|---|
| 1 | A | 1 | T | 1 |
| 4 | D | NULL | NULL | NULL |
查询2:结果
| ID | 名称 | ID | 名称 | ParentID |
|---|---|---|---|---|
| 1 | A | 1 | T | 1 |
| 2 | B | NULL | NULL | NULL |
| 3 | C | NULL | NULL | NULL |
| 4 | D | NULL | NULL | NULL |
我假设查询将返回相同的结果，而当查询没有返回时，我感到很惊讶.我更喜欢查询2的样式(而且我认为它是最佳选择)，但我认为查询将返回相同的结果.
I assumed the queries would return the same results and I was surprised when they didn't. I prefer the style of query 2 (and I think it is more optimal), but I thought the queries would return the same results.
(注意:很晚以后添加了带有数据的SQL示例，以澄清为什么这个问题不是另一个问题的重复，并使其达到当前的问题标准.样本结果更清楚地表明查询1返回具有1个或更多匹配子代的父母和没有孩子的父母.查询2返回所有父代，但仅匹配子代.很明显，我现在明白了查询之间的区别.)
(NOTE: The SQL example with data was added much later for clarification as to why this question is not a duplicate of another question, and to bring it up to current question standards. The sample results make it much clearer that Query 1 returns the parents with 1 or more matching children and parents with no children. Query 2 returns all parents but only matching children. Obviously I understand the difference between the queries now.)
编辑/摘要:
这里提供了一些很好的答案.我很难选择授予谁答案.我决定选择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个或多个匹配过滤器的孩子的父母
查询结果:
- 查询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提供了一个很好的链接(现已失效，因此请使用archive.org):
HLGEM provided a good link (now dead, so using archive.org):
推荐答案
第一个查询将返回父级没有子级或某些子级符合过滤条件的情况.具体而言，将省略父母有一个孩子但不符合过滤条件的情况.
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.
这篇关于ON和WHERE中的SQL外部联接筛选条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
