Minus Operation in MySQL

1. Emulate MINUS Operator in MySQL (8.0.27)
2. When to USE NOT IN, NOT EXISTS, and LEFT JOIN/IS NULL
3. Conclusion

The MINUS operator is used in SQL to find unique elements of table A that are not present in table B.

Going through this tutorial, we will see how to simulate the MINUS operator in MySQL to get the desired results. We will understand it by using NOT IN, NOT EXISTS, LEFT JOIN, and IS NULL.

We will also see the syntax of each of the mentioned methods and explore some differences between them.

Emulate MINUS Operator in MySQL (8.0.27)

Using NOT IN in MySQL

Let’s take an example of a student-course relationship, create a database and two tables within the database. One is named student, and the other is the course.

The student table has students’ ID, FIRST_NAME, LAST_NAME, GENDER, and the course table has COURSE_CODE, COURSE_TITLE, and STUDENT_ID columns.

Example Code:

/*
create the database and use it for table creation and other manipulation*/
CREATE SCHEMA db_practice_minus_operator;
USE db_practice_minus_operator;

# create student table
CREATE TABLE db_practice_minus_operator.student (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(64) NOT NULL,
    LAST_NAME VARCHAR(64) NOT NULL,
    GENDER VARCHAR(30) NOT NULL
);

#create course table
CREATE TABLE course (
    COURSE_CODE VARCHAR(60) NOT NULL,
    COURSE_TITLE VARCHAR(64) NOT NULL,
    STUDENT_ID INT NOT NULL,
    PRIMARY KEY(COURSE_CODE),
    FOREIGN KEY (STUDENT_ID) REFERENCES student(ID)
);

Remember, the course table only has science subjects, and we want to know the students who have not enrolled in any of the science courses. Let’s populate the tables and look at the student and course tables.

Example Code:

# populate student table
INSERT INTO
student(FIRST_NAME, LAST_NAME,GENDER)
VALUES
('Shaajeel', 'Daniel', 'Male'),
('Nayya', 'Preston', 'Female'),
('James', 'Robert', 'Male'),
('Jennifer', 'John', 'Female'),
('Sarah', 'Paul', 'Female'),
('Karen', 'Donald','Female'),
('Thomas', 'Christopher','Male'),
('Lisa', 'Mark', 'Female'),
('Anthony', 'Richard', 'Male'),
('Matthew', 'Charles', 'Male');

# populate course table
INSERT INTO
course(COURSE_CODE, COURSE_TITLE, STUDENT_ID)
VALUES
(125854, 'Biology', 1),
(542968, 'Mathematics', 2),
(125648, 'Computer Science', 5),
(654891, 'Physics', 4),
(483215, 'Chemistry', 8),
(147934, 'Artificial Intelligence',6);

The current data in both tables look as follows.

Student’s Table:

Course’s Table:

Now, write the following SQL query to understand the MINUS operation simulation in MySQL.

Example Code:

# Simulate Minus Operator in MySQL
SELECT * FROM student
WHERE student.ID NOT IN
(SELECT STUDENT_ID FROM course);

Output:

Using NOT EXISTS in MySQL

Example Code:

# Simulate Minus Operator in MySQL
SELECT * FROM student std
WHERE NOT EXISTS
(SELECT STUDENT_ID FROM course  WHERE std.ID = STUDENT_ID);

Output:

Using LEFT JOIN and IS NULL in MySQL

Example Code:

SELECT * FROM student
LEFT JOIN course on student.ID = course.STUDENT_ID
WHERE course.STUDENT_ID IS NULL;

Output:

In the above output, COURSE_CODE, COURSE_TITLE, and STUDENT_ID are visible as NULL because these students have not enrolled or registered for any courses.

You can also compare the ID, FIRST_NAME, LAST_NAME, and GENDER column with other methods’ output to see that everything is working as expected.

When to USE NOT IN, NOT EXISTS, and LEFT JOIN/IS NULL

The question now is how to select one of these three methods. You can decide based on a few basic points.

• The major difference among these three methods is that NOT IN and NOT EXISTS display the values only from the left table (the first select query).
• But the LEFT JOIN/IS NULL will output the left table as well as the NULL values instead of the right table’s values where there is no match found between the left and right table, because LEFT JOIN/IS NULL is used to retrieve data from more than one table.
• Another difference is how they handle the NULL values from the right table because LEFT JOIN/IS NULL and NOT EXISTS are semantically equivalent while NOT IN is not.
• NOT EXISTS returns TRUE if no row fulfilling the equality condition is found in the right table.
• NOT IN behaves differently; if a row is found in the list, IN will output TRUE, then NOT IN will return FALSE. On the other hand, if a row is not found in the list, then IN will return NULL, and NOT IN will also make NULL because negation to NULL is NULL.

Conclusion

Considering all the details discussed above, we have concluded that MySQL does not support the MINUS operation, but there are other ways to emulate the MINUS operator. You can use it as per your need, comfort, and requirements.