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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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.
