EXPERT RESPONSE
What to do?
The solution is to take the results of both the "left outer"
three-way query and the "right outer" three-way query, and UNION them.
This works most satisfactorily, because UNION eliminates duplicate results.
And of course there always will be duplicate results, because both the left
outer query and the right outer query will find all related rows.
The related rows would be returned twice.
For the sake of performance, it may be better to
avoid the sort required by UNION to eliminate duplicates.
Instead, use UNION ALL and ensure that
the related rows are returned by only one of the queries.
select pkA, Name, PkC, Colour
from tableA
left outer
join tableB
on pkA = BfkA
inner
join tableC
on BfkC = pkC
union all
select null, null, PkC, Colour
from tableB
right outer
join tableC
on BfkC = pkC
where BfkC is null
query result:
1 tom 35 green
1 tom 36 gray
2 bob 34 red
2 bob 35 green
3 amy -- -----
- --- 37 blue
- --- 38 white
An outer join that tests for nulls
in the keys of unmatched rows is called an exception join.
We are specifically looking for rows of tableC
that have no matching row in tableB. This also means there's
no point in having an inner join to tableA,
because the exception join is only
looking for rows of tableB that don't exist -- if
you know what I mean. But while we aren't joining to tableA,
the second query in the union must remain union-compatible, and
that's what the nulls in the SELECT list are for.
Generally speaking, a full outer join is a left or right outer join
unioned with an exception join. This is exactly the strategy
used to achieve "full outer" results
in database systems that support LEFT and RIGHT OUTER JOIN
syntax but not FULL. We have simply
extended this concept to solve the problem with three tables.
Besides, FULL OUTER JOIN syntax does not help
in the many-to-many situation anyway.
|
