misplacing of on condition in a join was executing successfully instead of throwing a syntax error

Question

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?

Answer 1

This is from Itzik Ben-Gan's book Inside Microsoft SQL Server 2008: T-SQL Querying

I find this explanation (picture including) very helpfull

Answer 2

Placing ON clauses like that is completely valid T-SQL syntax. It is often used when mixing inner and outer joins.

The only article I could find that references that syntax is Be Careful When Mixing INNER and OUTER Joins by Jeff Smith.

Almost nobody uses that syntax, because it is really unnecessarily complicated.

But see also Forcing Join Order Without Hints by Erik Darling.

Answer 3

You discovered the other join syntax :-)

Nobody actually uses it, but it's defined in Standard SQL. You can write all the JOINs first and then all the ONs, but the first table is joined in the last ON, strange and confusing.

t1 
join t2
join t3
on t3.col = t2.col
on t2.col = t1.col

Same using parentheses:

t1 
join (t2 
      join t3
      on t3.col = t2.col
     )
on t2.col = t1.col