Problem solve Get help with specific problems with your technologies, process and projects.

Full outer join in a many-to-many relationship, part 2

Here is the second part of Rudy's answer. See part 1.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.