gotchasqlModerate
View designer strange join syntax
Viewed 0 times
designerviewsyntaxjoinstrange
Problem
I have a data warehouse with a lot of views built in with the SSMS view designer.
Viewing the syntax, the
Changing the order of the various
Any suggestions or links for figuring out and fixing view designer queries please?
Viewing the syntax, the
FROM clauses are unintelligible to me because the ON statements do not follow straight after the relevant JOIN . 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?
Solution
This is just Standard SQL join syntax with the optional parentheses removed:
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.
SELECT *
FROM tableC
LEFT JOIN
(
TableB
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
)
ON TableB.TypeID = TableC.TypeIDIf 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.
Code Snippets
SELECT *
FROM tableC
LEFT JOIN
(
TableB
RIGHT JOIN TableA
ON TableA.ID = TableB.ID
)
ON TableB.TypeID = TableC.TypeIDContext
StackExchange Database Administrators Q#84580, answer score: 11
Revisions (0)
No revisions yet.