I've been provided this old SQL code (table names changed) to replicate, and the JOIN syntax isn't something I've seen before, and is proving hard to google:
select <stuff>
from A
inner join B
on A.ID = B.A_ID
inner join C -- eh? No ON?
inner join D
ON C.C_ID = D.C_ID
ON B.C_ID = D.C_ID -- a second ON here? what?
When I saw the code, I assumed I'd be sent broken code and it wouldn't run.
But it does. (Sql Server 2012)
What does it do? Is there a more sensible / standard way of writing it? What's happening here?
While unusual, this is perfectly valid tsql. Typically you see this approach when you have an outer join to a set of related tables which are inner joined to one another. A better IMHO way to write this would be:
inner join B
on A.ID = B.A_ID
inner join (C inner join D ON C.C_ID = D.C_ID)
ON B.C_ID = D.C_ID
This makes the join logic clear - and it also helps the reader. Additionally, it lets the reader know that the developer did this intentionally. So let this be an example of poor coding. Comment things that are unusual. Explain things. Have someone review your code periodically to help improve your style and usage.
And you could write this in a "typical" style by rearranging the order of tables in the from clause - but I'll guess that the current version makes more logical sense with the real table names.
I ran it by a colleague who figured it out:
select <stuff>
from A
inner join B
on A.ID = B.A_ID
inner join ( C -- put a bracket here...
inner join D
ON C.C_ID = D.C_ID
) -- and one here
ON B.C_ID = D.C_ID
or to format it a little nicer:
select <stuff>
from A
inner join B
on A.ID = B.A_ID
inner join (
C
inner join D
ON C.C_ID = D.C_ID
)
ON B.C_ID = D.C_ID
I wasn't familiar with this kind of "sub-join" (I don't know what it's called), but this is much more readable and clear
