*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
|
Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
|
Breck Carter
Last modified: May 22, 1996
mail to: bcarter@bcarter.com
Give yourself Extra Bonus Points if you know the answer without any further details. The rest of us need more information:
Two tables, parent and child, have a referential integrity relationship based on parent.parent_key and child.parent_key. Here are the rows:
parent table child table
------------ --------------------
parent_key parent_key child_key
a a x
a y
b
c c x
c y
d
As you can see, two parent rows b and d are childless, while the two
remaining rows a and c each have two dependent rows in child.
Here's the Watcom SQL select statement that attempts to join parent and child while preserving rows in parent that don't happen to have any dependent rows in the child table:
select parent.parent_key,
child.child_key
from parent left outer join child
where parent.parent_key = child.parent_key
Unfortunately, even though an outer join is used the result set is still
missing parent rows b and d. What's the problem?
parent_key child_key
a x
a y
c x
c y
The answer lies in the where clause. It cancels the effect of the outer
join by qualifying the whole select on child.parent_key and rejecting
candidate rows where that column is missing.
The solution is to move the where clause to the on condition of the from clause. In this location it means "include matching child rows but if there are no child rows at all, keep this parent row anyway":
select parent.parent_key,
child.child_key
from parent left outer join child
on parent.parent_key = child.parent_key
Here's the expected result set:
parent_key child_key
a x
a y
b
c x
c y
d
When someone says
"this outer join is not returning enough rows"
the answer is often
"check the where clause."
Other databases use different syntax for outer joins but the same problem is very common: Some part of the where clause is using an equality operator "=" instead of an outer join like "*=".
Check the where clause and look for comparisons that say "equals a column that might not exist".