Menu
Can I put a left outer join inside another left outer join? Like this:
SELECT * FROM table1
LEFT OUTER JOIN table2 ON (LEFT OUTER JOIN table 3 ON (Join Conditions))
WHERE
....(where conditions)
311
Yes, it is possible. We would use a query with two LEFT OUTER JOINs to retrieve the hierarchy.
An SQL query can JOIN multiple tables. For each new table an extra JOIN condition is added.
There really is no difference between a LEFT JOIN and a LEFT OUTER JOIN. Both versions of the syntax will produce the exact same result in PL/SQL. Some people do recommend including outer in a LEFT JOIN clause so it's clear that you're creating an outer join, but that's entirely optional.
The LEFT OUTER join will retrieve all rows from the table on the left of the join whether they match or not. In the subsequent INNER JOIN, obviously if something doesn't match up, you will lose some rows.
To group multiple joins, the syntax is as follows (untested on db2)
SELECT *
FROM table1 t1
LEFT JOIN (
table2 t2 INNER JOIN table3 t3 ON t3.someId = t2.someId
) ON t2.someId = t1.someId
Same syntax for left join inside LEFT JOIN(), but please read comment by @X-Zero
SELECT *
FROM table1 t1
LEFT JOIN (
table2 t2 LEFT JOIN table3 t3 ON t3.someId = t2.someId
) ON t2.someId = t1.someId
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
