This article will be going through a few examples to illustrate MySQL SELECT WHERE with NOT IN clause.
Table of Contents:-
- Syntax
- MySQL SELECT WHERE NOT IN LIST
- MySQL SELECT WHERE NOT IN SUBQUERY
- MySQL SELECT WHERE NOT IN ANOTHER TABLE
Let us get started by making the sample data used across the examples. We will be creating two tables student_college_details, student_personal_details followed by inserting some rows into them.
# create table student_college_details CREATE TABLE student_college_details ( student_id INT, student_college_name VARCHAR(50) ); # insert data to student_college_details INSERT INTO student_college_details(student_id,student_college_name) VALUES(1,"XVR Academy"), (2,"XVR Academy"), (3,"XVR Academy"), (4,"Learner's Intitute"), (5,"Learner's Intitute"), (6,"XVR Academy"), (7,"Learner's Intitute"), (8,"Learner's Intitute"), (9,"XVR Academy"); # create table student_personal_details CREATE TABLE student_personal_details ( student_id INT, student_name VARCHAR(255), student_ssn_no BIGINT, total_score INT ); # insert data to student_personal_details INSERT INTO student_personal_details(student_id,student_name,student_ssn_no,total_score) VALUES(1,'Daniel',1147483782,455), (2,'Sandy',1147483783,500), (3,'Veronica',1147483784,390), (4,'Jennifer',1147483785,280), (5,'Austin',1147483786,458), (6,'Veronica',1147483787,498), (7,'George',11474837988,200), (8,'Veronica',1147483789,232), (9,'Havard',1147483790,345), (10,'Mounica',1147483791,405), (11,'Will',1147483792,284), (12,"Ryma",1147483793,150);
To see what got added to both the tables execute:
SELECT * FROM student_college_details;
Output:-
Frequently Asked:

SELECT * FROM student_personal_details;
Output:-

We will be moving forward with the syntax followed by examples.
Syntax:-
SELECT * FROM tableName WHERE columnName NOT IN (value1, value2...);
MySQL SELECT WHERE NOT IN LIST
This section will have an example to illustrate the working of SELECT FROM a table WHERE the values are NOT IN a list.
Example: Get the personal details of all the students except Veronica and George
SELECT * FROM student_personal_details WHERE student_name NOT IN ('Veronica','George');
Action Output Message:-
8 row(s) returned.
Output:-

The output in image_3 shows that all rows except with student_name Veronica and George have been selected.
MySQL SELECT WHERE NOT IN SUBQUERY
This section will have an example to illustrate the working of SELECT FROM a table WHERE the values are NOT IN a subquery.
Example: Get the personal details of the students with maximum total_score
SELECT * FROM student_personal_details WHERE total_score IN (SELECT MAX(total_score) FROM student_personal_details);
Action Output Message:-
2 row(s) returned.
Output:-

The output in image_4 shows the rows which have the highest total_score. Since there were two students with maximum total_score, therefore two rows retrieved.
MySQL SELECT WHERE NOT IN ANOTHER TABLE
This section will have an example to illustrate the working of SELECT FROM a table WHERE the values are NOT IN another table. We will be making use of data from both the tables student_college_details and student_personal_details.
Example: Get the personal details of all students except those who have registered in student_college_details
SELECT * FROM student_personal_details WHERE student_id NOT IN (SELECT student_id from student_college_details);
Action Output Message:-
7 row(s) returned.
Output:-

The output in image_5 shows the rows which have student_id values not present in the table student_college_details.
READ MORE
- MySQL SELECT WHERE
- MySQL SELECT WHERE LIKE
- MySQL SELECT WHERE IN SubQuery
- MySQL Select where Count is greater than one [Solved]
We hope this article helped you with MySQL SELECT WHERE NOT IN queries. Good Luck!!!