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.

Cool to know right.. Read more on this Article @ SQL Team