I am breaking my head over this query construction. Basically my situation is I have 2 tables - (a) users, (b) registered_users. Now both the tables have a column called 'email'. '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. The twist here is for all the records in 'users', I need to display the email address from 'registered_users' always, as long as there is a record in it, otherwise, I need to display the email address from the 'users' table.

I am absolutely terrible in SQL. The only thing I can logically derive is that I, for sure, need an left outer join here. However, I am not able to figure out how to selectively extract values for the same column.

Can someone please help me out here.

An example schema is:

users: id, email, phone_number, created_on

registered_users: id, user_id, email, registered_on, status

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.