The SQL in Operator is used to restrict the number of rows (or records) returned by the SELECT Statement. The SQL Server IN Operator checks the given expression or Column name against the Values inside the IN. If there is at least one match, then SELECT Statement will return the records
SQL IN Operator Syntax
The syntax of the SQL Server IN operator is
SELECT [Column Names] FROM [Source] WHERE [Column Name] IN (Value1, Value2, ...., ValueN) --We can also use SQL OR operator to write the above statement SELECT [Column Names] FROM [Source] WHERE [Column Name] = Value1 OR [Column Name] = Value2 OR .... OR [Column Name] = Value
- Columns: It allows us to choose the number of columns from the tables. It may be one or more.
- Source: If one or more tables present in the Database, use JOINs to join multiple SQL Server tables.
- Values: Here, we have to provide the values or expression we want to check against the Column Name inside an in operator. If the column name matches with any Value, then only records will be displayed.
TIP: If you want to check the expression against Multiple values, use Sql Server IN Operator.
For this Sql Server IN Operator example, we are going to use the below-shown data
SQL IN Example 1
The following Sql Server in operator query will find all the Employees present in the Employees table whose [Yearly Income] is either 70000 or 80000
-- SQL Server IN Example USE [SQL Tutorial] GO SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE YearlyIncome IN (70000, 80000)
OUTPUT
Let us show you one more in operator example for better understanding.
SQL IN Operator Numerical Example 2
The following query returns the Employees whose Yearly Income is either 70000, 80000, or 90000
-- SQL Server IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE YearlyIncome IN (70000, 80000, 90000)
OUTPUT
SQL Where IN String Example 1
You can use any data type column inside the IN operator.
The following In operator query find all the Employees present in the Employees table whose Occupation is either Management or Clerical
-- SQL Server IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] IN ('Management', 'Clerical')
OUTPUT
SQL Where IN String Example 2
The below Where in string query returns the employees whose Occupation is either Management, Professional or Clerical
-- SQL Server IN Example USE [SQL Tutorial] GO SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] IN ('Management', 'Professional', 'Clerical')
OUTPUT
SQL IN Operator with Multiple Conditions
We can also use multiple operators in a single WHERE Clause. In this example, we are going to use SQL Server IN Operator (Arithmetic Operator) and OR Operator (Logical Operator) in a single statement.
The following query returns Employees whose Occupation is either Management or Professional Or their Yearly Income should equal to 50000
-- SQL Server IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] IN ('Management', 'Clerical') OR YearlyIncome = 50000
OUTPUT
SQL IN with Order By Clause
In SQL Server, you can use this IN along with the Order By Clause. Here, Order By Desc will sort the query result in descending order based on the yearly income column.
-- SQL Server IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] IN ('Management', 'Clerical') OR YearlyIncome = 50000 ORDER BY YearlyIncome DESC
OUTPUT
SQL Where IN Operator in Subquery
The SQL IN allows you to use it inside a Subqueries in Where Clause.
Before we start this example, let me show you another table that we use here
For example, the following in operator query will return all the Employees whose Occupation is present in the Employees 2015 table.
-- SQL Server IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] IN (SELECT [Occupation] FROM [Employees 2015])
OUTPUT
SQL NOT IN Example
In SQL, We can also use the NOT Keyword along with the IN operator. For example, the following query finds all the employees available in the employee table whose Occupation is NOT Management and Professional
-- SQL Server NOT IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] NOT IN ('Management', 'Professional')
OUTPUT
SQL NOT IN Example 2
You can also use this NOT IN operator on Numerical columns as well. Below query shows the employees whose yearly income is not 50000 or 80000
-- SQL Server NOT IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [YearlyIncome] NOT IN (50000, 80000)
OUTPUT
SQL NOT IN Example 3
In Sql Server, you can also write a Subquery inside the NOT IN operator.
-- SQL Server NOT IN Example SELECT [EmpID] ,[FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [Employee] WHERE [Occupation] NOT IN (SELECT [Occupation] FROM [Employees 2015])
OUTPUT