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



Not Enough Rows?

Today's Pop Quiz: You've just coded an outer join and everything looks OK, it passes the syntax check and runs successfully but just doesn't return all the rows you expect. Why?

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".


See also: Too Many Rows?


Breck Carter can be reached by phone at (416) 763-5200 or via email at bcarter@bcarter.com.