EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL NOT IN

MySQL NOT IN

By Priya PedamkarPriya Pedamkar

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.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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()
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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
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
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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 Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more