|
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:
| CustomerID | CustomerName |
| 3 | Barbara Spears |
| 9 | Maddalena Corvaglia |
| 17 | Bruce Davenport |
| 19 | Michael Ballack |
| 21 | Marlon Zeana |
| 25 | Xena 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)
);
|
|