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. See why not and how to avoid the confusion.