View designer strange join syntax

Question

I have a data warehouse with a lot of views built in with the SSMS view designer. Viewing the syntax, the FROM clauses are unintelligible to me because the ON statements do not follow straight after the relevant JOIN <table>. Often the principal table of the query is RIGHT joined and a random "type" table is specified in the FROM. So I have a situation like:

SELECT *
FROM tableC
LEFT JOIN TableB
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
ON TableB.TypeID = TableC.TypeID 
WHERE ....

Changing the order of the various JOIN and ON clauses to what I would have hand written changes the results in unexpected ways. And it would seem that most SQL prettyfiers choke on this ugly syntax but SSMS doesn't. I haven't been able to find rhyme, reason or documentation to the help unravel what is going on.

Any suggestions or links for figuring out and fixing view designer queries please?

Answer 1

This is just Standard SQL join syntax with the optional parentheses removed:

SELECT *
FROM tableC
LEFT JOIN 
(
    TableB
    RIGHT JOIN TableA
        ON TableA.ID = TableB.ID
)
ON TableB.TypeID = TableC.TypeID

If you don't like the syntax generated by the SSMS view designer (which is buggy and rarely updated anyway), simply write the views by hand using regular T-SQL.

Some people refer to this syntax style as 'nested join syntax'. Example blog post.