JIYIK CN >

MINUSOperator is used in SQL to find unique elements in table A that are not present in table B.

Through this tutorial, we will see how to emulate the operator in MySQL MINUSto get the desired result. We will understand it by using NOT IN, NOT EXISTS, , LEFT JOINand .IS NULL

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

MINUSEmulating the operator in MySQL (8.0.27)

Using in MySQLNOT IN

Let's take the student-course relationship as an example and create a database and two tables in the database. One is called 学生and the other is called course.

studentThe table has students ID', FIRST_NAME, LAST_NAME, GENDER, and coursethe table has COURSE_CODE, , COURSE_TITLEand STUDENT_IDcolumns.

Sample 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 that coursethe table only has science subjects and we want to know the students who did not take any science courses. Let's populate the table and view the studentand coursetables.

Sample 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 is shown below.

Student desk:

Course Schedule:

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

Sample code:

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

Output:

Using in MySQLNOT EXISTS

Sample code:

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

Output:

In MySQL, use LEFT JOINandIS NULL

Sample 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_TITLEand STUDENT_IDare displayed as NULLbecause these students have not registered or enrolled in any courses.

You can also compare the ID, FIRST_NAME, , LAST_NAMEand GENDERcolumns with the output from other methods to see if everything is working as expected.

When to use NOT IN, NOT EXISTSand LEFT JOIN/IS NULL

Now the question is how to choose one of these three methods. You can decide based on several basic points.

• The main difference between these three methods is that NOT INand NOT EXISTSdisplay only the values ​​from the left table (the first select query).
• But LEFT JOIN/ IS NULLwill output left table as well as NULLvalues ​​instead of right table's values ​​in case no match is found between left and right tables, because LEFT JOIN/ IS NULLis used to retrieve data from multiple tables.
• NULLAnother difference is how they handle values ​​in the right table , since LEFT JOIN/ IS NULLand NOT EXISTSare semantically equivalent, but NOT INis not.
• If no row satisfying the equality condition is found in the right table, NOT EXISTSreturn TRUE.
• NOT INbehaves differently; if a line is found in the list, INwill be output TRUE, and NOT INwill be returned FALSE. On the other hand, if a line is not found in the list, INwill be returned NULL, and NOT INwill also be returned NULL, because NULLthe negation of is NULL.

in conclusion

Considering all the details discussed above, we come to the conclusion that MySQL does not support MINUSthe operation, but there are other ways to simulate MINUSthe operation. You can use it according to your needs, comfort level, and requirements.