Clever Geek Handbook

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
+3


source to share


1 answer


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.

+3


source






More articles:


All Articles