7

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?

11

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.

| improve this answer | |
  • 1
    This is my favorite example of when it can cause different results: forum post – Erik Darling Dec 9 '14 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.