I'm normally pretty good at finding the answer I need by searching, but I'm drawing a blank on this one.
Let's say I have two tables that both have a list of names and ID numbers (we'll call them a and b). There is some other data in each of these tables which is why they are different tables, but for this query all I need is the name. What I currently have is something along the lines of:
SELECT c.number, c.field, a.name FROM c LEFT JOIN a ON (c.number = a.number)
However, it's possible that the number I'm looking for is not in a but is instead in b. How can I retrieve a.name is my number is in table a but b.name if the number is in table b? The code that loads this data (and other policies about this number) ensures that it will only exist in one table or the other.
Just join both:
select c.number, c.field, coalesce(a.name, b.name) name
from c
left join a on a.number = c.number
left join b on b.number = c.number
Some are suggesting you join a union instead:
select c.number, c.field, ab.name
from c
left join (
select a.number, a.name from a
union all
select b.number, b.name from b
) ab on ab.number = c.number
but that is unlikely to perform well.