performance query – MySQL in which appropriate scenario to use WHERE EXISTS

Not a fair test. Your example is about as close as possible between the use of JOIN use EXIST.

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.