How to perform a LEFT JOIN with a condition?

Question

MySQL

Hello. I have two tables:
messages - stores messages.
messages_read - stores data about whether the message was read by the user.
When I get a list of messages from the database and want to determine if the message has been read by the current user, I have to do a LEFT JOIN and the query comes out like this.

SELECT m.`id`, m.`message`, mr.`readed` 
FROM `messages` m
LEFT JOIN `messages_read` mr ON mr.`message_id` = m.`id` AND mr.`user_id` = 111
WHERE m.`chat_id` = 12

Please pay attention to AND mr.`user_id` = 111 . This condition is simply necessary for me, because I need to know whether this particular user has read this message.
But the MySql documentation says to avoid conditions in the JOIN construct.
Can you tell me what is the best way to deal with this situation?

Answer 1

If it were necessary to determine whether the user has read the message, it would make sense to do the selection exactly as you have, but specifying mr.`user_id` in the return fields, which would be either an id or null. If you have a LOT of people in the chat, then it would be more correct to do this:

SELECT m.`id`, m.`message`, mr.`message_id` status
FROM `messages` m
LEFT JOIN (
      select `message_id` 
      from `messages_read`
      where `user_id` = 111
) mr 
ON mr.`message_id` = m.`id` 
WHERE m.`chat_id` = 12

Answer 2

If the request fulfills in a time acceptable to you, then do not change anything.

Answer 3

SELECT m.`id`, m.`message`, case when mr.`message_id` is not null then 'Прочитано' else 'Не прочитано' end as iRead
FROM `messages` m
LEFT JOIN `messages_read` mr ON mr.`message_id` = m.`id`
WHERE m.`chat_id` = 12 and AND mr.`user_id` = 111

Answer 4

You can put the condition in where - modern optimizers in both cases will build the same optimal plan