stack stack - 1 year ago 47
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 Source

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.