SQL IN

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

City IN

(SELECT City FROM TownsAndCities WHERE City < 'LONDON')