You are currently viewing Difference Between IN and NOT IN Operators in SQL

Difference Between IN and NOT IN Operators in SQL

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
IN operator with single value
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
IN Operator with multiple Select value.png
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
IN Operator with
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
in operator with inner query
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.

NOT IN operator 1
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
NOT IN operator
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
NOT IN operator with string input

Recommended Readings

Buy Best Stationery Products in Pakistan

Piano Ballpoint Pen

Piano Ballpoint Pen
Shop Now

ORO Trimetal Pencil

ORO Trimetal
Shop Now

Dollar Clipper Pen

Shop Now

UHU 10×7 ml Pack of 10 PCs

UHU 10×7 ml Pack of 10 PCs
Shop Now

Glue Stick 8g Pack Of 12 PCs

Glue Stick 8g
Shop Now

Color Pencils Box of 12 PCs

Deer Multi Color Pencils
Shop Now
Tags: , , ,