I've noticed one of our programmers wrote this:
SELECT * FROM Table_A A
INNER JOIN Table_B B
INNER JOIN Table_C C
ON C.Id = B.Id
ON B.Id = A.Id
I didn't expect this to work but it does return results from our database.
Does this work the same as the normal join syntax? I can't find documentation on it.
Yes. The way I usually recommend people think about it is as if JOIN acts the same as an opening bracket ( and ON acts the same as a closing bracket ). Then the normal rules you're used to for bracket matching tell you which ON clause relates to which JOIN, and gives you some context for determining which table names/aliases are in scope.
So this query effectively joins table B to table C, then joins A to that combined result.
I'd normally recommend against it when purely using INNER JOINs - you can rearrange the tables easily so that you follow the more "normal" JOIN/ON/JOIN/ON pattern. You get the same logical result from this query1:
SELECT *
FROM Table_B B
INNER JOIN Table_C C
ON C.Id = B.Id
INNER JOIN Table_A A
ON B.Id = A.Id
Note that this is covered by the documentation, if you've read it carefully:
<table_source> ::= { table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] [ WITH ( < table_hint > [ [ , ]...n ] ) ] ... | <joined_table> ...
And
<joined_table> ::= { <table_source> <join_type> <table_source> ON <search_condition> ...
So, in a <joined_table>, what goes on the left of the <join_type>? Any <table_source>. And what goes on the right of the <join_type>? Any <table_source>. And either or both of those can, themselves, in fact be a <joined_table>.
The "normal" form you refer to in your question is what falls out if we only allow ourselves to use a <joined_table> in the left-hand position of another <joined_table>.
1Although if you're relying on the column order rather than column names, you'll get columns in a different order due to using SELECT *. But you shouldn't rely on column orders.
