Tutorial Gateway

SQL IN Operator

by

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 Operator 1

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

SQL IN Operator 2

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 IN Operator 3

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 IN Operator 4

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 5

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 Operator 6

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 IN Operator 7

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

SQL IN Operator 8

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 IN Operator 9

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 IN Operator 10

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 IN Operator 11

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

SQL IN Operator 12

Placed Under: SQL