Kbase P104280: SQL-92: How to implement a FULL OUTER JOIN between two tables?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/8/2005 |
|
Status: Unverified
GOAL:
SQL-92: How to emulate a FULL OUTER JOIN in SQL-92?
GOAL:
SQL-92: How to implement a FULL OUTER JOIN using the SQL-92 UNION statement?
FIX:
A FULL OUTER JOIN retrieves all of the records from both tables, even if there are no matching values for records in the other table. Progress SQL-92 does not currently support FULL OUTER JOIN syntax. However, a FULL OUTER JOIN can be implemented as a UNION between a LEFT OUTER JOIN and a RIGHT OUTER JOIN as per the following example:
1. The RIGHT OUTER JOIN: ( Retrieves all the records from the Order table even if there is no matching records in the Customer table)
SELECT
Customer.CustNum, Order.CustNum
FROM
Customer, Order
WHERE
Customer.CustNum = Order.CustNum (+)
2. The LEFT OUTER JOIN: (Retrieves all the records from the Customer table even if there is no matching records in the Order table)
SELECT
Customer.CustNum, Order.CustNum
FROM
Customer, Order
WHERE
Customer.CustNum (+) = Order.CustNum
3. The FULL OUTER JOIN emulation: (Retrieves all of the records from both tables, even if there are no matching values for records in the other table)
SELECT
Customer.CustNum, Order.CustNum
FROM
Customer, Order
WHERE
Customer.CustNum = Order.CustNum (+)
UNION
SELECT
Customer.CustNum, Order.CustNum
FROM
Customer, Order
WHERE
Customer.CustNum (+) = Order.CustNum