Domajno Domajno - 1 month ago 7
MySQL Question

MySQL distinguish between two cases depending on a subquery

I have a query which returns all events for a given user. But first, it searches for the user in a subquery given user's mobile number.

SELECT e.user_id, e.id FROM events e
WHERE e.user_id = (SELECT u.user_id FROM users u WHERE u.mobile = '88888888')


If there is no user with that mobile number 0 rows will be returned. If the user exists but there are no events 0 rows will be returned as well. I would like to be able to differentiate between those two cases. Any idea how I could do that in a one query?

Here is my approach:

(SELECT (SELECT u.user_id FROM users u WHERE u.mobile = '88888888'), NULL)
UNION
(SELECT e.user_id, e.id FROM events e
WHERE e.user_id = (SELECT u.user_id FROM users u WHERE u.mobile = '88888888'))


In that way I know that I will always have at least one row which just checks if the user exists. So if user_id in the first row is NULL means that there is no user and if it is not NULL means the user exists. But I do not like this solution because user search subquery is repeated twice and significantly slower then just the first query. Any ideas for a faster solution?

Answer

If you want a single result set which will show all events and users, regardless of whether an event maps to a user or vice-versa, then you can try a simulated full outer join:

SELECT COALESCE(u.user_id, 'NA'),
       COALESCE(e.id, 'NA')
FROM events e
LEFT JOIN users u ON e.user_id = u.user_id
WHERE u.mobile = '88888888'
UNION ALL
SELECT COALESCE(u.user_id, 'NA'),
       COALESCE(e.id, 'NA')
FROM events e
RIGHT JOIN users u ON e.user_id = u.user_id
WHERE u.mobile = '88888888'