CODEWITHSUNDEEP
sqloracle-database
cloudwalker
cloudwalker

Reputation: 2456

Oracle join to one table OR another

Is there a way to accomplish the following:

Table: PERSON
OID|NAME
--------
1  |John
2  |Mary

Table: COOL_PERSON
OID|PERSON_OID|...Other columns
--------------------------
5  |1         |...

Table: SMART_PERSON
OID|PERSON_OID|...Other columns
--------------------------
5  |1         |...

So, I know that both SMART_PERSON and COOL_PERSON have a PERSON_OID, and a bunch of other columns that are shared in between the two tables. At the moment I have a very large query that is UNIONing the results between those two tables, but what I was wondering is if there is a way to say "join to whichever table the PERSON_OID exists in"? That way I wouldn't have to UNION the exact same query where the only difference is that in one I'm joining to COOL_PERSON and in the other I'm joining to SMART_PERSON. Hope that makes sense.

Upvotes: 0

Views: 219

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

The solution is two left joins:

select p.*, coalesce(cp.col1, sp.col1) as col1,
       coalesce(cp.col2, sp.col2) as col2,
       . . .
from person p left join
     cool_person cp
     on p.oid = cp.person_oid left join
     smart_person sp
     on p.oid = sp.person_oid and cp.person_oid is null;

This should have better performance than the union approach. However, you should consider fixing your data model. The common columns should probably go in person.

Upvotes: 1

Related Questions