Kbase P76591: SQL-92: LEFT OUTER JOIN appears not to return all records from the LEFT table!
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/17/2005 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1D
SYMPTOM(s):
Trying to join all records from table A to table B with table B records having a field restriction.
Result set contains records where table B field restriction is satisfied but not all of table A records
The original SELECT statement was similar to:
SELECT
Customer.Custnum,
Customer.Name,
Order.SalesRep
FROM
Customer LEFT OUTER JOIN Order ON Order.Custnum = Customer.Custnum
WHERE
Order.SalesRep = 'HXM'
CAUSE:
The SELECT statement is filtering the results of the join. The left outer join correctly built the result set containing all records from the Customer table, however, since the WHERE clause is applied after the join was completed it filtered out any record where Order.SalesRep is NOT 'HXM'.
FIX:
Modify the SELECT statement as follows:
SELECT
Customer.Custnum,
Customer.Name,
Order.SalesRep
FROM
Customer LEFT OUTER JOIN Order
ON Order.Custnum = Customer.Custnum
AND Order.SalesRep = 'HXM'This causes the selection criteria to be fully evaluated within the join operation as opposed to after the join is completed