CODEWITHSUNDEEP
sql-server
Dijal
Dijal

Reputation: 47

Can a SQL join have on clause not immediately after the join but later in the query?

I came up with these queries when I was looking to model the data based on the views. Queries are similar to the example below. As my understanding SQL join will be followed by ON clause. Here the joins are not immediately followed by ON clause. And when I ran these views they work fine on the database, giving out the result dataset. Can anyone explain this query please?

Select *
FROM B  
INNER JOIN C     
INNER JOIN D  
ON C.c_id = D.d_id   
INNER JOIN E   
ON C.c_id = E.e_id  
INNER JOIN F   
ON E.e_id = F.f_id ON B.b_id = D.d_id

Upvotes: 1

Views: 123

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

I more commonly use this pattern in LEFT JOIN/INNER JOIN situations. For example, imagine a situation where a customer's address is optional but, when an address is provided, the city is required and will always exist. 

SELECT c.CustomerName, ca.AddressLine1, cy.CityName
    FROM Customer c
        LEFT JOIN CustomerAddress ca
            INNER JOIN City cy
                ON ca.CityID = cy.CityID
            ON c.CustomerID = ca.CustomerID;

And, as others have mentioned, parentheses can make the intent clearer.

SELECT c.CustomerName, ca.AddressLine1, cy.CityName
    FROM Customer c
        LEFT JOIN (CustomerAddress ca
            INNER JOIN City cy
                ON ca.CityID = cy.CityID)
            ON c.CustomerID = ca.CustomerID;

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

Yes it is perfectly valid syntax... It's the telltale sign that someone's been using the graphical Query Designer in SSMS.

Because no sane human would ever write t-sql like, you can be at least 99.9% sure it's machine generated t-sql.

Upvotes: 0

Dominic P
Dominic P

Reputation: 2901

Yes, this is possible. The query you have there can be made clearer by using parentheses:

Select *
FROM B  
INNER JOIN 
(
    C     
    INNER JOIN D  
        ON C.c_id = D.d_id   
    INNER JOIN E   
        ON C.c_id = E.e_id  
    INNER JOIN F   
        ON E.e_id = F.f_id
) 
ON B.b_id = D.d_id

You can read more about various join strategies here, if you're interested. This particular case is illustrated as the third option.

Edit: I should note that this may not work in all database engines, nor do I find it a particularly elegant way to write a query. Still, it is valid in some contexts at least.

Upvotes: 2

Related Questions