SQL IN

Prev
Next

SQL IN operator

IN operator is used to test for a value that matches any one of the values from a fixed set of values.

If you use the OR operator for the same column multiple times –

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel
FROM Production.Product
WHERE ProductID = 1 OR ProductID = 10 OR ProductID = 15 OR ProductID = 20;
Here, we have used the OR operator multiple times. The better way to code this query is by using the IN operator-

Example

SELECT ProductId, Name, StandardCost, ListPrice, SafetyStockLevel
FROM Production.Product
WHERE ProductID  IN (1, 10, 15, 20);

This time, I wish to select the StateProvince where State Province code is either AK, AZ, CO, MB or ID, then you can simply code as –

Example

SELECT *
FROM Person.StateProvince
WHERE StateProvinceCode IN ('AK', 'AZ', 'CO', 'MB', 'ID');

NOT IN Operator

In the same way, if I wish to get all the StateProvince which do not fall within AK, AZ, CO, MB or ID, then I can simply use NOT IN operator for this purpose.

SELECT *
FROM Person.StateProvince
WHERE StateProvinceCode NOT IN ('AK', 'AZ', 'CO', 'MB', 'ID');
Prev
Next
Facebook Twitter

Tutorials for all brains!