debugsqlMinor
misplacing of on condition in a join was executing successfully instead of throwing a syntax error
Viewed 0 times
misplacingexecutingerrorconditionjoinsyntaxinsteadthrowingwassuccessfully
Problem
I have executed the following CTE with joins
the result of the above query was as follows:
The execution plan for the query was as follows:
I was happy with the result, now I cross checked my query which I have used above, identified that there was a misplaced near on condition.
The misplaced join was as follows:
As per my understanding there must be a
After I changed the on condition and verify the both results and execution plan both were same.
Can any one please explain why this thing happens, how sql server was executing?
;with cte(asd) as(select 601
union all
select 602)
,cte2(fgh) as (select 601 union all
select 602 union all
select 603 union all
select 604 union all
select 605 union all
select 606 union all
select 607 union all
select 608 union all
select 609 union all
select 610 union all
select 611 union all
select 612)
select p.* from cte2 p
inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asdthe result of the above query was as follows:
The execution plan for the query was as follows:
I was happy with the result, now I cross checked my query which I have used above, identified that there was a misplaced near on condition.
The misplaced join was as follows:
inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asdAs per my understanding there must be a
on condition after inner join, but sql server didn't throw an error but executed and results were as expected. After I changed the on condition and verify the both results and execution plan both were same.
;with cte(asd) as(select 601
union all
select 602)
,cte2(fgh) as (select 601 union all
select 602 union all
select 603 union all
select 604 union all
select 605 union all
select 606 union all
select 607 union all
select 608 union all
select 609 union all
select 610 union all
select 611 union all
select 612)
select p.* from cte2 p
inner join cte c
on p.fgh=c.asd
inner join cte2 c2 on c.asd=c2.fghCan any one please explain why this thing happens, how sql server was executing?
Solution
This is from Itzik Ben-Gan's book Inside Microsoft SQL Server 2008: T-SQL Querying
I find this explanation (picture including) very helpfull
I find this explanation (picture including) very helpfull
Context
StackExchange Database Administrators Q#179992, answer score: 3
Revisions (0)
No revisions yet.