Lukas Eder explains COUNT versus EXISTS:
COUNT(*) needs to return the exact number of rows. EXISTS only needs to answer a question like:
“Are there any rows at all?”
In other words, EXISTS can short-circuit after having found the first matching row. If your client code (e.g. written in Java or in PL/SQL, or any other client language) needs to know something like:
“Did actors called “Wahlberg” play in any films at all?”
Lukas shows how it works in Oracle and Postgres; the result is still basically the same for SQL Server.