Omit JOIN if condition is met
How do I convert this request to handle the case where s.userId is -1, in which case the JOIN should be omitted?
SELECT s.*, u.type AS userType, u.email AS userEmail
FROM session s
JOIN users u ON ( u.user_id = s.userId )
WHERE ( s.storeId = ? )
ORDER BY s.timeUpdated DESC
Use LEFT JOIN
:
SELECT s.*, u.user_type AS userType, u.email AS userEmail
FROM session s
LEFT JOIN users u ON u.user_id = s.userId
WHERE ( s.storeId = ? )
ORDER BY s.timeUpdated DESC
Note that there will be some strange behavior if there is a user with an id -1
, but I assume this will never happen. We usually save null
instead of a dummy value, such as -1
to indicate that "no" exists (in this case, "no user").
It doesn't really matter for joins, but by making the field null and storing null values, you can also create a foreign key constraint on the users table so that other non-null IDs are actually checked against the table. This is a way to ensure that your data is valid. Without such restrictions, you can also insert -5 or 300000 even if those users don't exist, which can lead to nasty surprises later.