SQL Server IN command seems deceptively simple. But what can it do exactly?
There are two main uses;
List type which is equivalent to a string of ORs (Expression)
Sub-Query IN Select (Subquery)
Example 1 - Expression
SELECT Address1 FROM Personnel WHERE City IN ('London', 'Birmingham')
is the same as
SELECT Address1 FROM Personnel WHERE City = 'London' or City = 'Birmingham'
However the fun really starts when you get into Subqueries. Subqueries are always only allowed to return one column...
Example 2 - Subquery
SELECT Address1 FROM Personnel WHERE
(SELECT City FROM TownsAndCities WHERE City < 'LONDON')