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');
Note
You can also use the NOT IN operator to select records not present within the sets of elements
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');