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!
source to share
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.
source to share