left outer join: get column values selectively-CodePudding

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 users table.

I need a left outer join. But how do I selectively extract values for the same column?

CodePudding user response：

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.