Strange / esoteric join syntax

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.

  • T-SQL Move From Test To Production Dynamically
  • How would I configure Effort Testing Tool to mock Entity Framework's DbContext withOut the actual SQL Server Database up and running?
  • Call dll function from sql stored procedure using the current connection
  • Extracting Username\password from text\properties file in VBScript
  • LIKE statement with Table variable
  • Why is SQL Server 2008 blocking SELECT's on long transaction INSERT's?
  • But it does. (Sql Server 2012)

    What does it do? Is there a more sensible / standard way of writing it? What’s happening here?

  • Microsoft SQL 'not like' vs <>
  • Performance for RBAR vs. set-based processing with varying transactional sizes
  • how to find differences in the sequence between two tables?
  • return only one row for complex multi join sql
  • SQL Server beginner: How to update table data from another table?
  • Query returns a different result every time it is run
  • 2 Solutions collect form web for “Strange / esoteric join syntax”

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.