将筛选条件添加到外部联接而不是where子句有什么区别? [英] What is the difference when adding a filter criteria to an outer join instead of a where clause?
问题描述
我试图在外连接子句中使用过滤器而不是where子句过滤出一个表。当我尝试这样做,我得到意想不到的结果。整个表被返回,就像我没有应用过滤器。
当我运行这个例子,我得到不同的结果为最后两个查询。我希望他们有相同的结果,但事实并非如此。这是怎么回事?
declare @a表
(
id int
, content varchar(100)
)
declare @b表
(
id int
,content varchar(100)
)
insert into @a(id,content)values(1,'Apple')
insert into @a(id,content)values(2,'Banana')
insert into @a(id ,内容)值(3,'橙')
插入@b(id,内容)值(1,'果汁')
插入到@b(id,内容)值(2, Peel')
插入@b(id,content)值(3,'Julius')
- 基本外连接
select * from @aa left join @bb对于a.id = b.id
- 使用where子句过滤的外部连接
从@aa中选择* left在a.id = b.id中加入@bb其中a.id = 1
- 使用连接子句过滤器的外部连接
从@aa中选择* left在a.id = 1和a.id = b.id中添加@bb
允许外连接返回NULL,而在返回结果之前,必须匹配where子句。
select * from @aa left a.id = b.id其中a.id = 1
转换为给我所有来自 a
其中 id = 1
并尝试将此关联到 b
其中 a.id = b.id
。
select * from @aa在a.id = 1和a.id = b.id上加入@bb
on另一方面,转换为给我所有行 a
,如果 a.id = 1
,尝试将它与 b
中的任何行相关联,其中 a.id = b.id
(否则只要给出<
将其与内部联接进行对比,其中将条件添加到 ON
子句并将其添加到 WHERE
子句是同义词。
I'm trying to filter out a table using filter in the outer join clause rather than a where clause. When i try to do so i'm getting unexpected results. The whole table is returned as though i didn't apply the filter at all.
When I run this example, i get different results for the last two queries. I would expect them to have the same results but it's not the case. What is going on here?
declare @a table
(
id int
,content varchar(100)
)
declare @b table
(
id int
,content varchar(100)
)
insert into @a (id,content) values (1,'Apple')
insert into @a (id,content) values (2,'Banana')
insert into @a (id,content) values (3,'Orange')
insert into @b (id,content) values (1,'Juice')
insert into @b (id,content) values (2,'Peel')
insert into @b (id,content) values (3,'Julius')
--basic outer join
select * from @a a left join @b b on a.id=b.id
--outer join with where clause filter
select * from @a a left join @b b on a.id=b.id where a.id=1
--outer join with join clause filter
select * from @a a left join @b b on a.id=1 and a.id=b.id
An outer join is allowed to return NULL for the joined table's row, whereas a where clause must be matched before the result is returned.
select * from @a a left join @b b on a.id=b.id where a.id=1
translates to "Give me all rows from a
where id=1
and try to correlate this with any rows in b
where a.id=b.id
.
select * from @a a left join @b b on a.id=1 and a.id=b.id
on the other hand, translates to "Give me all rows from a
and, if a.id=1
, try to correlate this with any rows in b
where a.id=b.id
(otherwise just give me the data from a
).
Contrast this with an inner join, where adding a condition to the ON
clause and adding it to the WHERE
clause is synonymous.
这篇关于将筛选条件添加到外部联接而不是where子句有什么区别?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!