SQL Server Articles

Submit Article
Home » Articles » SQL Server » Operator RSS Feeds

IN Operator

Posted By: Sarita Patel     Category: SQL Server     Views: 2231

This article explains use of IN operator with different examples.

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)


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


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



Didn't find what you were looking for? Find more on IN Operator Or get search suggestion and latest updates.

Sarita Patel
Sarita Patel author of IN Operator is from United States.
View All Articles

Please enter your Comment

  • Comment should be atleast 30 Characters.
  • Please put code inside [Code] your code [/Code].

No Comment Found, Be the First to post comment!