Before we learn IN and NOT IN operators in SQL, First create below table tblEmployee and populate data.
USE HRDB GO CREATE TABLE tblEmployee ( EmpID INT PRIMARY KEY IDENTITY(1,1), EmpName VARCHAR(20), EmpGender VARCHAR(10), EmpDept VARCHAR(250), EmpAge INT, EmpSalary INT, EmpJoiningDate DATETIME ) GO
Let’s populate the tblEmployee table with data.
USE HRDB GO INSERT INTO tblEmployee (EmpName, EmpGender, EmpDept, EmpAge, EmpSalary, EmpJoiningDate) VALUES ('Davio Nancy','Male','HR',25,50000,'2010-01-01'), ('Fuller Andrew','Male','HR',20,40000,'2010-06-01'), ('Leverling Janet','Male','Research and Development',35,50000,'2010-07-01'), ('King Robert','Male','Purchase',22,25000,'2010-01-01'), ('Ali Hassan','Male','HR',30,75000,'2010-08-01'), ('Zahid Ali','Male','Research and Development',25,50000,'2011-01-01'), ('Sara Ali','Female','Sales',25,50000,'2011-09-01'), ('Suman Shah','Female','HR',20,30000,'2011-12-01'), ('Anne Andrew','Female','Purchase',33,35000,'2012-01-01'), ('Ammara Khan','Female','Sales',25,50000,'2013-01-01') GO
SQL IN Operator
SQL IN operator is used in the WHERE clause or in sub-query in SQL. Basically, we compare the column values with the IN operator values enclosed inside the parenthesis. If the column values matched with the IN operator values then certain operations can be performed. Let’s see the following queries for better understanding.
Write SQL query to retrieve records from tblEmployee whose salary is 25000 using IN operator
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary IN (25000) GO
Result

Write SQL query to retrieve records from tblEmployee whose salary is 25000 and 75000 using IN operator
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary IN (25000,75000) GO
Result

Write SQL query to retrieve records from tblEmployee whose salary is 25000, 50000 and 75000 using IN operator
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary IN (25000,50000,75000) GO
Result

Write SQL query to retrieve records from tblEmployee whose salary is 50000 using inner query.
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary IN (SELECT EmpSalary FROM tblEmployee WHERE EmpSalary=50000) GO
Result

Write SQL query to retrieve records from tblEmployee who belongs to HR and Sales department.
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpDept IN ('HR','Sales') GO
Result

SQL NOT IN Operator
NOT IN operator is used to get rows which are not meeting the values of IN operator inside the parenthesis.
write SQL query to retrieve records from tblEmployee whose salary not equal to 25000
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary NOT IN (25000) GO
Above query will retrieve all the rows except those employees whose salary is 25000.

write SQL query to retrieve all records from tblEmployee except 25000 and 75000 using NOT Operator
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary NOT IN (25000,75000) GO
Above query will retrieve all the rows except those employees whose salary is 25000 and 75000.
Result

Write SQL query to retrieve all records from tblEmployee except 25000,50000 and 75000 using NOT IN Operator in inner query.
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpSalary NOT IN (SELECT EmpSalary FROM tblEmployee WHERE EmpSalary IN (25000,50000,75000)) GO
Above query will retrieve all the rows except those employees whose salary is 25000, 50000 and 75000. In this query we used NOT IN with outer query and IN operator in the inner query.
Result

SQL Query with strings used in IN operator
USE HRDB GO SELECT * FROM tblEmployee WHERE EmpDept NOT IN ('HR','Sales') GO
Above query will retrieve all the rows except those employees who does not belongs to HR and Sales.
Result

Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL