August 15, 2008 at 10:21 am
Why we get different out here.
select 2,null union all
select null,'XYZ'
insert @t2
select 1,'MNO' union all
select null,'JKL' union all
select 3,'XYZ1'
--Querr1
select *
from @t1 a
left join @t2 b
on isnull(a.ids,'')=isnull(b.ids,'')
and a.ids <> 0
--Querr2
select *
from @t1 a
left join @t2 b
on isnull(a.ids,'')=isnull(b.ids,'')
where a.ids <> 0
August 15, 2008 at 10:58 am
Nick123 (8/15/2008)
Why we get different out here.select 2,null union all
select null,'XYZ'
insert @t2
select 1,'MNO' union all
select null,'JKL' union all
select 3,'XYZ1'
--Querr1
select *
from @t1 a
left join @t2 b
on isnull(a.ids,'')=isnull(b.ids,'')
and a.ids <> 0
--Querr2
select *
from @t1 a
left join @t2 b
on isnull(a.ids,'')=isnull(b.ids,'')
where a.ids <> 0
what's the definition of: @t1 and @t2 ?
* Noel
August 15, 2008 at 2:49 pm
The difference is because Joins are done before Wheres. The Join clause eliminates certain rows that the Where clause would allow, or the Join clause includes certain rows that the Where clause would get rid of. It's the sequence.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2008 at 3:06 pm
You can look in SQL join types chapter Difference between predicates in ON clause vs. predicates in WHERE clause for a theoretical algorithm how joins are being performed and why for outer joins there IS important where to put predicates.
Gints Plivna
http://www.gplivna.eu
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply