EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL NOT IN
 

MySQL NOT IN

Priya Pedamkar
Article byPriya Pedamkar

Updated July 1, 2023

MySQL NOT IN

 

 

Introduction to MySQL NOT IN

MySQL NOT IN function is used as a subquery statement that guarantees that the given expression does not contain any values passed as arguments in the function. MySQL NOT IN clause selects all records or rows from the table that do not match the values passed in the NOT IN function. It eliminates all the records or rows containing the values that get matched with those passed in the function and print only those records other than those matched.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

Expression NOT IN (value1, value2, value3……valuen)

Using MySQL NOT IN Keyword with WHERE Clause

When the where clause is used with the NOT IN keyword in the MySQL statement, it does not affect the rows that do not match the values provided to the NOT IN function as arguments.

Examples of MySQL NOT IN

To know the concept of mysql NOT IN function concept, we will create a table named “students.”

Below is a query for creating a statement:

Code:

create table students (roll_no int, student_name varchar (150), course varchar (150));

After creating the table, students, now it’s time to insert values into the table. Once the table is created, we can now insert values into it.

Below is the query for the insert statement:

Code:

Insert into students values (1,'ashish','java');
Insert into students values (2,'rahul','C++');
Insert into students values (3,'divya','Arch');
Select * from students;

Output:

MySQL NOT IN 1

Example #1

Below is the query of the mysql IN function with the where clause, which selects only the rows or records whose values match the values passed in the mysql IN function as arguments.

Code:

Select * from students where roll_no IN (1, 2, 3);

The above query will select all records from table students where the roll_no of students is either 1 or 2, or 3.

Output:

where roll_no of students is either is 1 or 2 or 3

Example #2

NOT IN keyword works simply opposite to IN clause. Since the IN clause will select all the records that contain or match the values passed in the IN function, the NOT IN will eliminate all the records that match the values passed in the function and selects all the records that are not present in the values of the function.

Below is the query of mysql NOT IN with where clause:

Code:

Select * from students where roll_no NOT IN (1, 2);

Output:

where roll_no of students is not either 1 or 2

The above query will select all rows from table students where the roll_no of students is not either 1 or 2.

MySQL IN functions and NOT In function both works with multiple column strategy.

Example #3

To explain MySQL IN and NOT IN functions, we will take another table named employee and project.

Code:

create table employee(e_id int primary key,e_name varchar(150),Address varchar(150));
insert into employee values(1,'ashish','delhi');
insert into employee values(2,'aman','lucknow');
insert into employee values(3,'somya','banglore');
insert into employee values(4,'sonika','chennai');
insert into employee values(5,'ajay','hyderabad');
insert into employee values(6,'rahul','noida');
select * from employee;

Output:

MySQL NOT IN 4

Code:

create table project(e_id int not null,p_id int primary key,e_name varchar(150),Address varchar(150));
insert into project values(1,101,'java','chennai');
insert into project values(5,102,'c++','lucknow');
insert into project values(3,103,'payments','delhi');
insert into project values(4,104,'oauth','chennai');
insert into project values(4,105, 'wallet', 'gurgoan');
select * from project;

Output:

MySQL NOT IN 5

To find the detail of an employee whose address is either delhi, banglore, or Noida.

Code:

Select * from employee where address IN('delhi','banglore','noida');

The above query will select all records from the table employee where the employee’s address is either delhi, banglore, or Noida.

Output:

MySQL NOT IN 6

Code:

Select * from employee where address NOT IN('delhi','banglore','noida');

The above query will eliminate all records that have the employee’s address, either delhi, banglore, or Noida, and prints all records other than these three whose address is delhi, banglore, or noida.

Output:

eliminate all records that have address of employee either as delhi or banglore or Noida

Use of IN and NOT IN keywords in Subqueries

Find the names of employees who are working on a project.

Code:

Select e_name from employee where e_id IN(Select distinct e_id from project);

The above query will first select rows with distinct employee IDs from the project table and select the employee name from the employee table that matches the employee id of the project table.

Output:

MySQL NOT IN 8

Code:

Select e_name from employee where e_id NOT IN (Select distinct e_id from project);

The above query will first select rows that do not have a distinct employee id from the project table and then select an employee name from the employee table that matches the selected employee id of the project table.

Output:

MySQL NOT IN 9

So, this will select only two employee names, i.e., Aman and Rahul, as an output.

Conclusion

In this article, we saw about MySQL NOT IN keyword. We also saw the NOT IN clause in the main query in the select statement with the where clause and how to use it in the subquery statement. We also saw about MySQL IN clause, which helps us understand the NOT IN query much easier. This article also contains simple examples and screenshots of the output, which allows the reader to understand it properly.

Recommended Articles

We hope this EDUCBA information on “MySQL NOT IN” benefited you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL CTE
  2. MySQL CHECK Constraint
  3. Datetime in MySQL
  4. MySQL DATE_SUB()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW