Kbase 19850: SQL-89 - Order of Left Outer Join and Inner Join in a SELECT
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
13/04/2010 |
|
SUMMARY:
This solution applies to SQL-89 Progress Version 8.3B
When a left outer join is followed by the inner joins in a single SELECT statement, the results are identical to having all inner joins on the same tables.
The following code is supposed to display all customer records with/without having order records but when it is run only customer records that have associated orders is displayed. The left outer join is not working.
The code:
SELECT
Customer.Cust-Num,
Order.Order-num,
Order-Line.Order-num,
Order-Line.Line-num
FROM
sports.Customer LEFT OUTER JOIN sports.Order ON
Customer.Cust-Num = Order.Cust-Num
INNER JOIN sports.Order-Line Order-Line ON
Order.Order-num = Order-Line.Order-num
SOLUTION:
Use a UNION to combine results of two joins so that all customer records would get displayed as follows:
SELECT
Customer.Cust-Num,
order.order-num
FROM
sports.Customer
LEFT OUTER JOIN sports.Order ON
Customer.Cust-Num = Order.Cust-Num
UNION
SELECT
Order-Line.Order-num,
Order-Line.Line-num
FROM
sports.Order-Line INNER JOIN order ON
Order.Order-num = Order-Line.Order-num
References to Written Documentation:
SQL Guide and Reference.