IN operator compares
given value to the any values in a list or subquery.
Syntax of IN Operator :
test_expression [ NOT ] IN ( subquery| expression [ ,...n ] )
test_expression
is expression in sql server.
Subquery is a subquery that
has a result set of one column. This column must have the same data type as test_expression.
expression [,...n] is a list of
expressions to test for a match. All expressions must be of the same type as test_expression.
Return type of IN operator is Boolean. If the
value of test_expression is equal to any value returned by subquery or is equal to anyexpression from the comma-separated list, the
result value is TRUE. Otherwise, the result value is FALSE.
Using NOT IN
negates the returned value.
Examples of IN
Operator :
Example 1 :
Using IN operator in where clause
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice IN (10,15,20)
Output
ProductName UnitPrice
Aniseed
Syrup 10.00
Sir Rodney's
Scones 10.00
Maxilaku 20.00
Outback
Lager 15.00
Röd Kaviar 15.00
Longlife
Tofu 10.00
Above
example displays product having unit price 10 or 15 or 20. You can achieve same
result set using OR operator. To view this example, please visit below link.
OR Operator
Example 2 :
Using IN operator with subquery in where clause
SELECT P.ProductName, P.UnitPrice
FROM Products P
WHERE P.ProductName IN(SELECT
S.ProductName FROM Sales_by_Category S
WHERE P.ProductName
= S.ProductName)
ORDER BY P.ProductName
Output
ProductName UnitPrice
Boston Crab
Meat 18.40
Gnocchi di
nonna Alice 38.00
Gudbrandsdalsost 36.00
Outback
Lager 15.00
Schoggi
Schokolade 43.90
Thüringer
Rostbratwurst 123.79
Tourtière 7.45
Above query
shows use of in operator to get products and unitprice which are sold. You can
also achieve same result using Exists and = condition with ANY operator. To
view this example, please visit below link.
EXISTS
Operator
ANY Operator