CODEWITHSUNDEEP
sqloracle-database
drc15
drc15

Reputation: 103

Can I choose what table to JOIN based on a CASE?

For instance, if I have a table OWNER like this:

+-------------------------------+
|  ID  CatID   DogID   FishID   |
+-------------------------------+
|  1   464    (null)  (null)    |
|  2  (null)  (null)    93      |
+-------------------------------+

and a query similar to this:

select *
from owner
join cat
on owner.catid = cat.id
join dog
on owner.dogid = dog.id
join fish
on owner.fishid = fish.id;

But instead of joining every time, can I use a CASE statement (or similar) to say "only join to CAT if the value in the CatID column is not null". Is this possible?

Each owner ID will only have ONE of a cat, dog, or fish (i.e. for each owner, only one will be not null).

Upvotes: 1

Views: 60

Answers (2)

A ツ
A ツ

Reputation: 1267

i'd go with 3 left joins and make sure i have an index on each animal.id but you could try a union like:

select * 
from owner join cat on owner.catid = cat.id 
where owner.catid is not null
union all
select * 
from owner join dog on owner.dogid = dog.id 
where owner.dogid is not null
union all
select * 
from owner join fish on owner.fishid = fish.id
where owner.fishid is not null;

i seriously doubt it'll be faster

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270723

No, but you can use left join so you get all the values:

select owner.*, coalesce(cat.col, dog.col, fish.col) as col
from owner left join
     cat
     on owner.catid = cat.id left join
     dog
     on owner.dogid = dog.id left join
     fish
     on owner.fishid = fish.id;

You can then choose the (first) one that matches in the select.

Upvotes: 3

Related Questions