Oracle Logbook

May 1, 2012

How to overcome outer joins boundaries.

Filed under: Uncategorized — heliodias @ 6:49 am

Once in a while, you need to use outer joins and you face some limitations regarding semi-joins or something else.

The solution is not elegant, but is quite simple indeed.

Create your own “outer join”,  which means instead of join straight to the desired table, create a union between the desirable table and the original table (discarding the duplicate by using an anti-join (not exists /not in)

select t1.id,texto from l3_tb1 t1,
(
select id,texto from l3_tb2
union all
select t3.id,” from l3_tb1 t3 where
t3.id not in (select id from l3_tb2)
)
t2 where t1.id=t2.id;

Just remember that this solution is more resource intensive than the “real” outer join.

 

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: