Internal SQL connections within external connections; Search results nesting

I'm a little surprised that these two queries give different results:

First request:

SELECT a.number, a.name , b.*
FROM Atable a
LEFT OUTER JOIN Btable b
JOIN Ctable c ON c.number = b.number
ON b.number = a.number
ORDER BY a.number;

      

Second query:

SELECT a.number, a.name , b.*
FROM Atable a
LEFT OUTER JOIN Btable b ON b.number = a.number
JOIN Ctable c ON c.number = b.number
ORDER BY a.number

      

I expect both of these to return the results that the first query is executing. The first query returns each row from TableA; however, unexpectedly, the second row returns the results of TableA if they also exist in TableC.

Why does the join C to B restrict TableA in the second query but not in the first query?

Thank!

+3


source to share


1 answer


Your first request, using parens to figure out how it is parsed:

SELECT a.number, a.name , b.*
FROM Atable a LEFT OUTER JOIN
     (Btable b JOIN
      Ctable c
      ON c.number = b.number
     ) ON b.number = a.number
ORDER BY a.number;

      

Having two on

sentences in a line is confusing, so parentheses help. This makes it clear that you are saving all rows from the first table.

Second query:



SELECT a.number, a.name , b.*
FROM (Atable a LEFT OUTER JOIN
      Btable b
      ON b.number = a.number
     ) JOIN
     Ctable c
     ON c.number = b.number
ORDER BY a.number;

      

You are joining the result of the first join. Therefore, only rows that match are included in the result set.

When doing multiple joins, I recommend using left join

for all joins. Mixing internal and external connections can be confusing.

+3


source







All Articles