How do I conditionally select a field from one of two tables?

Question

I have two tables invoices and pending_payments both of which have the following rows in common: invoice_id and balance. I want to do a select in MySQL that'll work thus:

[pseudo-code]

if(invoice_id exists in pending_payments table) {
    select balance from pending_payments where invoice_id = yadayadayada
} else {
    select balance from invoices where invoice_id = yadayadayada
}

Is this even doable in MySQL? If so, how?

Answer 1

select i.invoice_id, coalesce(pp.balance, i.balance) as Balance
from invoices i
left outer join pending_payments pp on i.invoice_id = pp.invoice_id

Let me know if there can be multiple rows in pending_payments for the same invoice_id and I will propose an alternate solution.

Answer 2

I like OrbMan's solution (I think it is the most intuitive), but here is another way -- select all from one table and then the ones that you have not selected from the 2nd.

select invoice_id, balance from pending_payments where invoice_id = yadayadayada
UNION
select invoice_id, balance from invoices where invoice_id = yadayadayada
  AND invoice_id not in (select invoice_id from pending_payments)

and as a side note, some have stated that on some systems this is actually faster. I make no such claims, but if speed is an issue it is worth trying.

Also, if you need to include a source column (eg pending or invoice) it is easy to do it this way like this:

select 'pending' as source, invoice_id, balance from pending_payments where invoice_id = yadayadayada
UNION
select 'invoice' as source, invoice_id, balance from invoices where invoice_id = yadayadayada
  AND invoice_id not in (select invoice_id from pending_payments)