MySQL SELECT WHERE NOT IN

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

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!!!