stack stack - 2 months ago 6
MySQL Question

How does JOIN work even when there isn't any matches

I have this query:

UPDATE users u
LEFT JOIN events e ON e.author_id = u.id
SET u.events = 0, e.seen = 1
WHERE u.id = ?


My query updates
users
table even when this condition
... ON e.author_id = u.id
doesn't match any row. Now I need to also update
events
when there isn't any matched row in
users
table. I can do that by
RIGHT JOIN
. But I want to know can I mix both
RIGHT JOIN
and
LEFT JOIN
together?

Answer

You can't do that in MySQL, because MySQL doesn't support FULL OUTER JOIN.

But why not just run two update statements:

UPDATE users u 
    SET u.events = 0
    WHERE  u.id = ?;

UPDATE events e 
    e.seen = 1
    WHERE e.author_id = ?;

You can wrap these in a transaction, if you think the transactional semantics are important and your storage engine supports transactions.

Comments