SQL Server: JOIN vs IN vs EXISTS - the logical difference

SQL Server: JOIN vs IN vs EXISTS - the logical difference:

There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.
This is simply not true. To see why not, let's review what each statement does.

IN: Returns true if a specified value matches any value in a subquery or a list.
Exists: Returns true if a subquery contains any rows.
Join: Joins 2 resultsets on the joining column.