Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that looks like this:

    SELECT *
      FROM TBLA A
 LEFT JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

In which order will the joins be resolved? I'm most interested in SQL Server, and will mark an explanation for it as the answer, but am equally interested in the ANSI/ISO standard and how it works in the various RDBMS.

The reason for this question was to figure out why the results differed from this query

    SELECT *
      FROM TBLA A
CROSS JOIN TBLC C
 LEFT JOIN TBLB B ON A.Col1 = B.Col2 AND B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6
share|improve this question
    
CROSS JOIN TBLC C in your second query is very different from RIGHT JOIN TBLC C in your first one. That alone is a huge difference. –  A-K Nov 3 '12 at 17:23
    
Mixing LEFT and RIGHT JOIN in the same query is begging for confusion, IMHO. –  Jon of All Trades Jul 25 at 16:37

2 Answers 2

up vote 6 down vote accepted

Logically the joins are resolved in the order of the ON clauses from left to right.

The output of each join is a virtual table that goes into the next join.

So for the query in your question the virtual table result of A LJ B is then right joined onto C. The join condition of B.Col3 = C.Col4 will lose any null extended rows preserved by the original left join effectively turning the first join back into a inner join and the resulting virtual table (which preserves all rows from C) is then inner joined onto D.

So your initial query can be simplified as

SELECT *
FROM TBLA A
INNER JOIN TBLB B ON A.Col1 = B.Col2
RIGHT JOIN TBLC C ON B.Col3 = C.Col4
      JOIN TBLD D ON C.Col5 = D.Col6

Which is effectively the same as (A IJ B) ROJ (C IJ D)

The order of the ON clauses is not necessarily the same as the order the tables appear in the query. This could also be rewritten as (C IJ D) LOJ (A IJ B)

SELECT *
FROM   TBLC C
       INNER JOIN TBLD D
         ON C.Col5 = D.Col6
       LEFT JOIN TBLA A
                 INNER JOIN TBLB B
                   ON A.Col1 = B.Col2
         ON B.Col3 = C.Col4 

The position of the on clauses means that the outer join is carried out between the two virtual tables resulting from (C IJ D) and (A IJ B) rather than just on a single table.

In your second query conceptually the virtual table A x C is left joined onto B preserving the entire cartesian product then the result of that is joined onto D with the predicate C.Col5 = D.Col6. This eliminates any rows from the final result that do not inner join between C and D meaning it is equivalent to

SELECT *
FROM   TBLC C
       JOIN TBLD D
         ON C.Col5 = D.Col6
       CROSS JOIN TBLA A
       LEFT JOIN TBLB B
         ON A.Col1 = B.Col2
            AND B.Col3 = C.Col4 
share|improve this answer

In PostgreSQL:

Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM items.

http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-FROM

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.