Not a fair test. Your example is about as close as possible between the use of
In addition, this probably gave a different result – And if there were 2 Johns? the
JOIN would count both; the
EXIST would only count once.
EXIST is a "semi-join". It's better than
JOIN in cases where he can stop analyzing all the corresponding lines. Instead, he stops with the first game. If there is a useful index, the semi-join does not necessarily help much. Otherwise, it could be a big acceleration.
In the old days,
IN (SELECT ...) was notoriously inefficient.
EXISTS (SELECT *) is often a better choice.
Search around stackoverflow.com for more instances of
EXIST. I think you will find a variety.