SQL Server - IN Operator

The SQL Server (Transact-SQL) IN Operator is used to specify multiple values in a WHERE clause of SQL Server statement. It is a shorthand for multiple OR conditions.

Syntax

The syntax for using IN operator in SQL Server (Transact-SQL) is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example:

Consider a database containing a table called Employee with the following records:

• To select all records of the Employee table where City is London, Paris or Amsterdam, the query is given below.

  SELECT * FROM Employee
  WHERE City IN ('London', 'Paris', 'Amsterdam');
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  3	Jo	Paris	27	2800
  4	Kim	Amsterdam	30	3100

• Multiple values for the IN operator can also be specified using SELECT statement.

  SELECT * FROM Employee
  WHERE City IN (SELECT City from Employee);
  

  
  

  This will produce the result as shown below:

  EmpID	Name	City	Age	Salary
  1	John	London	25	3000
  2	Marry	New York	24	2750
  3	Jo	Paris	27	2800
  4	Kim	Amsterdam	30	3100
  5	Ramesh	New Delhi	28	3000
  6	Huang	Beijing	28	2800