I have executed the following CTE with joins
;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.asd
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:
inner join cte c
inner join cte2 c2 on c.asd=c2.fgh
on p.fgh=c.asd
As 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.fgh
Can any one please explain why this thing happens, how sql server was executing?