CODEWITHSUNDEEP
sqlsql-serverjoin
Inglocines
Inglocines

Reputation: 201

What does it mean when there are two ON conditions in an inner join and a table without any join condition

I came across a weird join condition and wanted help on this.

I have a SQL code like this and want to know what happens:

SELECT A.a,
       B.b
FROM   A
       INNER JOIN B
               ON A.a = B.b
       RIGHT OUTER JOIN C
                        INNER JOIN D
                                ON C.c = D.d
                        INNER JOIN E
                                ON D.d = E.e
                     ON C.c2 = A.a2

This works but I can't figure out what will happen in the above condition. Will C table be a right join or inner join? And what is the purpose of placing the on condition after an inner join. Does this make any difference?

What is the order of join execution on this?

Version: SQL Server 2016

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

It is quite unorthodox to write JOIN conditions like this. Although it is allowed, having two ON conditions together and not having an ON immediately following a JOIN is just confusing.

More so for people than SQL parsers, though. When parsing complex joins, you can imagine that there are parentheses:

SELECT A.a, B.b
FROM (A INNER JOIN
      B
      ON A.a = B.b
     ) RIGHT OUTER JOIN
     (C INNER JOIN
      D
      ON C.c = D.d INNER JOIN
      E
      ON D.d = E.e
     )
     ON C.c2 = A.a2;

These parentheses are redundant. They are showing how the SQL gets parsed.

Now your question is about the RIGHT JOIN condition and table C. The RIGHT JOIN is to the result of the INNER JOIN on C, D, and E.

So, neither of these are true statements (in general):

  • This is an "inner join" on C.
  • This is a "right outer join" on C.

Because the outer join is to the result of the inner join on the three tables.

If you can, I would suggest rewriting the condition to be clearer! At the very least, inserting the parentheses (although redundant) make the code easier for humans to understand.

Upvotes: 3

Related Questions