I have 2 tables –
users: id, email, phone_number, created_on
registered_users: id, user_id, email, registered_on, status
users will always have a record, while a record will be inserted in
registered_users only when the user is registered.
My requirement is to list all users, registered or not, and show their email addresses. For all the records in
users, I need to display the email address from
registered_users as long as there is a record in it; otherwise, I need to display the email address from the
I need a left outer join. But how do I selectively extract values for the same column?
SELECT COALESCE(r.email, u.email) AS email FROM users AS u LEFT OUTER JOIN registered_users AS r USING (id)
I’m assuming a given value of
id corresponds to the same user in both tables.
COALESCE() returns its first non-null argument.
If there is no matching row in registered_users, then the outer join returns NULL for all columns, and in that case, the select defaults to the users.email. If there is a matching row in registered_users, then its email will be returned first.
Answered By – Bill Karwin