迹忆客 专注技术分享

MINUS 运算符在 SQL 中用于查找表 A 中不存在于表 B 中的唯一元素。

通过本教程，我们将看到如何在 MySQL 中模拟 MINUS 运算符以获得所需的结果。我们将通过使用 NOT IN、NOT EXISTS、LEFT JOIN 和 IS NULL 来理解它。

我们还将看到每个提到的方法的语法，并探讨它们之间的一些差异。

在 MySQL (8.0.27) 中模拟 MINUS 运算符

在 MySQL 中使用 NOT IN

让我们以学生-课程关系为例，在数据库中创建一个数据库和两个表。一个叫学生，另一个叫 course。

student 表有学生的 ID、FIRST_NAME、LAST_NAME、GENDER，course 表有 COURSE_CODE、COURSE_TITLE 和 STUDENT_ID 列。

示例代码：

/*
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)
);

请记住，course 表只有科学科目，我们想知道没有参加任何科学课程的学生。让我们填充表并查看 student 和 course 表。

示例代码：

# 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);

两个表中的当前数据如下所示。

学生桌：

课程表：

现在，编写以下 SQL 查询来了解 MySQL 中的 MINUS 操作模拟。

示例代码：

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

输出：

在 MySQL 中使用 NOT EXISTS

示例代码：

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

输出：

在 MySQL 中使用 LEFT JOIN 和 IS NULL

示例代码：

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

输出：

在上述输出中，COURSE_CODE、COURSE_TITLE 和 STUDENT_ID 显示为 NULL，因为这些学生尚未注册或注册任何课程。

你还可以将 ID、FIRST_NAME、LAST_NAME 和 GENDER 列与其他方法的输出进行比较，以查看一切是否按预期工作。

何时使用 NOT IN、NOT EXISTS 和 LEFT JOIN/IS NULL

现在的问题是如何选择这三种方法中的一种。你可以根据几个基本的点来决定。

• 这三种方法的主要区别在于 NOT IN 和 NOT EXISTS 仅显示左表（第一个选择查询）中的值。
• 但是 LEFT JOIN/IS NULL 将输出左表以及 NULL 值而不是右表的值在左右表之间找不到匹配项的情况下，因为 LEFT JOIN/ IS NULL 用于从多个表中检索数据。
• 另一个区别是它们如何处理右表中的 NULL 值，因为 LEFT JOIN/IS NULL 和 NOT EXISTS 在语义上是等价的，而 NOT IN 不是。
• 如果在右表中没有找到满足相等条件的行，NOT EXISTS 返回 TRUE。
• NOT IN 的行为不同；如果在列表中找到一行，IN 将输出 TRUE，然后 NOT IN 将返回 FALSE。另一方面，如果在列表中没有找到一行，那么 IN 将返回 NULL，并且 NOT IN 也将返回 NULL，因为对 NULL 的否定是 NULL。

结论

考虑到上面讨论的所有细节，我们得出的结论是 MySQL 不支持 MINUS 操作，但还有其他方法可以模拟 MINUS 操作。你可以根据自己的需要、舒适度和要求使用它。