34,588
社区成员
发帖
与我相关
我的任务
分享在left join的on中设置条件和在where中设置条件有什么区别呀？
我用实例数据库AdventureWorks做了两个查询，如下：
查询1：
select s.SalesPersonID,SUM(s2004.SalesQuota) Total_2004_SQ,SUM(s2003.SalesQuota) Total_2003_SQ
From Sales.SalesPerson s
left join Sales.SalesPersonQuotaHistory s2004
on s.SalesPersonID=s2004.SalesPersonID and YEAR(s2004.QuotaDate)=2004
left join Sales.SalesPersonQuotaHistory s2003
on s.SalesPersonID=s2003.SalesPersonID and YEAR(s2003.QuotaDate)=2003
group by s.SalesPersonID
查询2：
select s.SalesPersonID,SUM(s2004.SalesQuota) Total_2004_SQ,SUM(s2003.SalesQuota) Total_2003_SQ
From Sales.SalesPerson s
left join Sales.SalesPersonQuotaHistory s2004
on s.SalesPersonID=s2004.SalesPersonID
left join Sales.SalesPersonQuotaHistory s2003
on s.SalesPersonID=s2003.SalesPersonID
where YEAR(s2004.QuotaDate)=2004 and YEAR(s2003.QuotaDate)=2003
group by s.SalesPersonID
这两个查询的区别就是第一个将YEAR(s2004.QuotaDate)=2004和YEAR(s2003.QuotaDate)=2003写在了on语句里，而第二个查询则将YEAR(s2004.QuotaDate)=2004和YEAR(s2003.QuotaDate)=2003写在了where语句里。这两个查询的执行结果是一样的，难道这两种写法没有区别？我记得应该是在ON里被筛掉的行会在select里被抓回来，而在where里写的不会呀。
查询1：
select s.SalesPersonID,SUM(s2004.SalesQuota) Total_2004_SQ,SUM(s2003.SalesQuota) Total_2003_SQ
From Sales.SalesPerson s
left join Sales.SalesPersonQuotaHistory s2004
on s.SalesPersonID=s2004.SalesPersonID and YEAR(s2004.QuotaDate)=2004
left join Sales.SalesPersonQuotaHistory s2003
on s.SalesPersonID=s2003.SalesPersonID and YEAR(s2003.QuotaDate)=2003
group by s.SalesPersonID
查询2：
select s.SalesPersonID,SUM(s2004.SalesQuota) Total_2004_SQ,SUM(s2003.SalesQuota) Total_2003_SQ
From Sales.SalesPerson s
left join Sales.SalesPersonQuotaHistory s2004
on s.SalesPersonID=s2004.SalesPersonID
left join Sales.SalesPersonQuotaHistory s2003
on s.SalesPersonID=s2003.SalesPersonID
where YEAR(s2004.QuotaDate)=2004 and YEAR(s2003.QuotaDate)=2003
group by s.SalesPersonID
这两个查询的区别就是第一个将YEAR(s2004.QuotaDate)=2004和YEAR(s2003.QuotaDate)=2003写在了on语句里，而第二个查询则将YEAR(s2004.QuotaDate)=2004和YEAR(s2003.QuotaDate)=2003写在了where语句里。这两个查询的执行结果是一样的，难道这两种写法没有区别？我记得应该是在ON里被筛掉的行会在select里被抓回来，而在where里写的不会呀。
...全文
请发表友善的回复…
发表回复
lingyun1314 2010-11-25
- 打赏
- 举报
可正统来说应该先执行完from语句然后再执行where呀，那如果where里面有t1.a=t2.b这类比较肯定应该在join后执行呀，那么where岂不是要执行两次。
fpzgm 2010-11-25
- 打赏
- 举报
[Quote=引用 12 楼 lingyun1314 的回复:]
sql server 的执行顺序是先执行where后进行join还是先join后where呢？还是随机决定的？怎么判定被join on筛选去掉的记录会不会被select回来呀？
引用 8 楼 nice_dream_li 的回复:
SQL code
CREATE TABLE a (a1 INT,a2 INT )
INSERT INTO a
SELECT 1,1 UNION AL……
[/Quote]
又是个看不全答案的人，看#7了吗
sql server 的执行顺序是先执行where后进行join还是先join后where呢？还是随机决定的？怎么判定被join on筛选去掉的记录会不会被select回来呀？
引用 8 楼 nice_dream_li 的回复:
SQL code
CREATE TABLE a (a1 INT,a2 INT )
INSERT INTO a
SELECT 1,1 UNION AL……
[/Quote]
又是个看不全答案的人，看#7了吗lingyun1314 2010-11-25
- 打赏
- 举报
sql server 的执行顺序是先执行where后进行join还是先join后where呢？还是随机决定的？怎么判定被join on筛选去掉的记录会不会被select回来呀？
[Quote=引用 8 楼 nice_dream_li 的回复:]
SQL code
CREATE TABLE a (a1 INT,a2 INT )
INSERT INTO a
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3
CREATE TABLE b (b1 INT,b2 INT )
INSERT INTO b
SELECT 1,1 UNION ALL
SELECT ……
[/Quote]
[Quote=引用 8 楼 nice_dream_li 的回复:]
SQL code
CREATE TABLE a (a1 INT,a2 INT )
INSERT INTO a
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3
CREATE TABLE b (b1 INT,b2 INT )
INSERT INTO b
SELECT 1,1 UNION ALL
SELECT ……
[/Quote]
zxjnew 2010-11-25
- 打赏
- 举报
学习了，方便优化sql。
luoyefeng1022 2010-11-25
- 打赏
- 举报
表连接 left join on 和 Where 是有区别的；但如果考虑的连接条件就没有区别！
你所说的结果不一样，是因为表连接（left join on 和 Where ）不一样，所以结果不一样！
就连接条件 On 和Where 是一样的！
你所说的结果不一样，是因为表连接（left join on 和 Where ）不一样，所以结果不一样！
就连接条件 On 和Where 是一样的！
流氓兔 2010-11-25
- 打赏
- 举报
SELECT *
FROM dbo.System_StaffInfo a LEFT JOIN dbo.StaffAnnualLeave b ON a.StaffNo = b.StaffNo AND b.TokenLeaveDays=0
ORDER BY a.StaffNo
SELECT *
FROM dbo.System_StaffInfo a LEFT JOIN dbo.StaffAnnualLeave b ON a.StaffNo = b.StaffNo
WHERE b.TokenLeaveDays = 0
ORDER BY a.StaffNo
以上的查询结果集是不同的，根据SQL 2005技术内幕，从逻辑上讲应该是先from然后再where,这样就很好理解了！然后看看查询计划，SQL优化得很牛X的！
nice_dream_li 2010-11-25
- 打赏
- 举报
CREATE TABLE a (a1 INT,a2 INT )
INSERT INTO a
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,3
CREATE TABLE b (b1 INT,b2 INT )
INSERT INTO b
SELECT 1,1 UNION ALL
SELECT 3,3
SELECT * FROM a
LEFT JOIN b ON a1=b1 AND a1=b1
a1 a2 b1 b2
----------- ----------- ----------- -----------
1 1 1 1
2 2 NULL NULL
3 3 3 3
SELECT * FROM a
LEFT JOIN b ON a1=b1
WHERE a1=b1
a1 a2 b1 b2
----------- ----------- ----------- -----------
1 1 1 1
3 3 3 3
fpzgm 2010-11-25
- 打赏
- 举报
给你举个例子，看了就明白
create table t1(id int, feild int);
insert into t1 values(1 , 1);
insert into t1 values(1 , 2);
insert into t1 values(1 , 3);
insert into t1 values(1 , 4);
insert into t1 values(2 , 1);
insert into t1 values(2 , 2);
create table t2(id int, feild int);
insert into t2 values(1 , 1);
insert into t2 values(1 , 2);
insert into t2 values(1 , 5);
insert into t2 values(1 , 6);
insert into t2 values(2 , 1);
insert into t2 values(2 , 3);
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id
--取t1表的第一行,扫瞄t2表,按条件做对比,如果满足条件,就加入返回结果表.
--然后取t1表的第二行,扫瞄t2表,按条件做对比,如果满足条件,就加入返回结果表.
--重复以上过程,直到t1表扫描结束.
/*
id feild id feild
1 1 1 1
1 1 1 2
1 1 1 5
1 1 1 6
1 2 1 1
1 2 1 2
1 2 1 5
1 2 1 6
1 3 1 1
1 3 1 2
1 3 1 5
1 3 1 6
1 4 1 1
1 4 1 2
1 4 1 5
1 4 1 6
2 1 2 1
2 1 2 3
2 2 2 1
2 2 2 3
*/
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t1.feild=1
--给左表加条件的时候,左表满足条件的,按上面的过程返回值,左表不满足条件的,直接输出,右表的
列补null
/*
id feild id feild
1 1 1 1
1 1 1 2
1 1 1 5
1 1 1 6
1 2 NULL NULL
1 3 NULL NULL
1 4 NULL NULL
2 1 2 1
2 1 2 3
2 2 NULL NULL
*/
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id where t1.feild=1
--先执行where后连接查询
--执行where后表为 1 , 1
-- 2 , 1
--用它来left join t2.
/*
id feild id feild
1 1 1 1
1 1 1 2
1 1 1 5
1 1 1 6
2 1 2 1
2 1 2 3
*/
--下面三条语句查询结果是一样的,当为右表加条件的时候,可以把left join 改为inner jin, 因为
inner join比left join 要快!
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id and t2.feild=1
select t1.*,t2.* from t1 left join t2 on t1.id=t2.id where t2.feild=1
select t1.*,t2.* from t1 inner join t2 on t1.id=t2.id and t2.feild=1
/*
id feild id feild
1 1 1 1
1 2 1 1
1 3 1 1
1 4 1 1
2 1 2 1
2 2 2 1
*/
drop table t1
drop table t2
nice_dream_li 2010-11-25
- 打赏
- 举报
嗯，on 条件 的筛选不是最终的结果，还有可能在接下来的outer join 中被带回来，而where 是最终的，移除了就没了 在SQL SERVER 2005技术内幕 T-SQL 查询 里面 有个例子的 楼主可以自己去看看
旅行者I号 2010-11-25
- 打赏
- 举报
inner join 的话没有区别，right（left） join 会有区别
lingyun1314 2010-11-25
- 打赏
- 举报
可我看SQL SERVER 2005技术内幕里说ON筛掉的行会在select里被抓回来呀。
gogodiy 2010-11-25
- 打赏
- 举报
--执行顺序
1、FROM
2、on
3、join--做笛卡尔乘积
4、WHERE
5、group BY
6、with(cube | rollup )
7、HAVING
8、select 列表
9、DISTINCT
10、order by
1、FROM
2、on
3、join--做笛卡尔乘积
4、WHERE
5、group BY
6、with(cube | rollup )
7、HAVING
8、select 列表
9、DISTINCT
10、order by
claro 2010-11-25
- 打赏
- 举报
先on 后where
旅行者I号 2010-11-25
- 打赏
- 举报
先将连接的表做笛卡尔积
然后用on条件过滤笛卡尔积
（left和right才会有此步，inner没有）然后根据连接方向补充不满足on条件的行，无匹配的字段值用null填充
然后where条件过滤
区别也是由于第三部造成的
然后用on条件过滤笛卡尔积
（left和right才会有此步，inner没有）然后根据连接方向补充不满足on条件的行，无匹配的字段值用null填充
然后where条件过滤
区别也是由于第三部造成的
