SQL simple -> Using list of values as condition in WHERE clause

Some time we need to use query criteria that is more complex than simple CustomerName='Paris Otton'. Some time we want to filter result set using large number of values in criteria. For example we want to get Customer records when the first letter of the name is equal to 'B' Or 'M' Or 'X'. The well known way to do this is following:

SELECT CustomerID, CustomerName
FROM Customers
WHERE MID(CustomerName,1,1) = 'B'
  OR MID(CustomerName,1,1) = 'M'
  OR MID(CustomerName,1,1) = 'X';

The query returns results like these:
CustomerIDCustomerName
3Barbara Spears
9Maddalena Corvaglia
17Bruce Davenport
19Michael Ballack
21Marlon Zeana
25Xena Aspin

Very convinient way to write same query is following:
SELECT CustomerID, CustomerName
FROM Customers
WHERE MID(CustomerName,1,1) IN ('B','M','X');

And also we can write subquery inside the brackets of the IN (...). Subquery had to return single column dataset. For example:
SELECT CustomerID, CustomerName
FROM Customers
WHERE MID(CustomerName,1,1) IN
(
  SELECT MID(CustomerName,1,1) AS FirstChar
  FROM Customers
  WHERE Country = 'new zealand' AND street = 'sodwarf end'
  GROUP BY MID(CustomerName,1,1)
);




sqlexamples.info