SQL IN Operator 9

SQL IN Operator

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 IN operator is

SELECT [Column Names]
FROM [Source]
WHERE [Column Name] IN (Value1, Value2, ...., ValueN)

--We can also use 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

If you want to check the expression against Multiple values, use the SQL IN Operator.

  • 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 within 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.

For this Sql IN Operator example, we are going to use the below-shown data

Employee Table Data 1

SQL IN Operator Example

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

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE YearlyIncome IN (70000, 80000)
SQL IN Operator 2

Let us show you one more SQL in operator example for better understanding.

The following query returns the Employees whose Yearly Income is either 70000, 80000, or 90000

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE YearlyIncome IN (70000, 80000, 90000)
SQL IN Operator 3

SQL Where IN String Example

You can use any data type column inside this keyword. This In operator query find all the Employees present in the Employees table whose Occupation is either Management or Clerical

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')
Where IN String 4

The below Where in string query returns the employees whose Occupation is either Management, Professional or Clerical

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 
 'Professional', 'Clerical')
SQL IN Operator on string Columns 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) and OR (Logical) within a single statement. The following query returns Employees whose Occupation is either Management or Professional Or their Yearly Income should equal to 50000

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')
  OR YearlyIncome = 50000
SQL IN Operator with Multiple Conditions 6

SQL IN with Order By Clause

Use this along with the Order By Clause. Here, Order By Desc will sort the query result in descending order based on the yearly income column.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN ('Management', 'Clerical')
  OR YearlyIncome = 50000
  ORDER BY YearlyIncome DESC
IN with Order by 7

SQL Subquery IN Operator

It allows you to use inside a Subqueries of Where Clause. Before we start this example, let me show you another table that we use here

SQL Subquery IN Operator 8

For example, the following query will return all the Employees whose Occupation is present in the Employees 2015 table.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] IN (SELECT [Occupation] 
 FROM [Employees 2015])
SQL IN Operator inside a Subquery 9

SQL NOT IN Example

We can also use the NOT Keyword along with the IN operator. For example, the following query finds all the employees available within the employee table whose Occupation is NOT Management and Professional

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] 
 NOT IN ('Management', 'Professional')
NOT in Example 10

You can also use this one on Numerical columns as well. Below query shows the employees whose yearly income is not 50000 or 80000

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [YearlyIncome] NOT IN (50000, 80000)
SQL IN Operator 11

We can also write a Subquery inside the NOT IN.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employee]
  WHERE [Occupation] NOT IN (SELECT [Occupation] 
 FROM [Employees 2015])
SQL IN Operator 12