MySQL 中的减法运算

Mehvish Ashiq 2023年1月30日 2022年5月13日
  1. 在 MySQL (8.0.27) 中模拟 MINUS 运算符
  2. 何时使用 NOT INNOT EXISTSLEFT JOIN/IS NULL
  3. 结论
MySQL 中的减法运算

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

通过本教程,我们将看到如何在 MySQL 中模拟 MINUS 运算符以获得所需的结果。我们将通过使用 NOT INNOT EXISTSLEFT JOINIS NULL 来理解它。

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

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

在 MySQL 中使用 NOT IN

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

student 表有学生的 IDFIRST_NAMELAST_NAMEGENDERcourse 表有 COURSE_CODECOURSE_TITLESTUDENT_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 表只有科学科目,我们想知道没有参加任何科学课程的学生。让我们填充表并查看 studentcourse 表。

示例代码:

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

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

学生桌:

mysql 中的减法操作——学生表数据

课程表:

mysql 中的减法操作——课程表数据

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

示例代码:

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

输出:

mysql 中的减法操作-不在

在 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 中的减法操作 - 不存在

在 MySQL 中使用 LEFT JOINIS NULL

示例代码:

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

输出:

mysql 中的减法操作-左连接并且为空

在上述输出中,COURSE_CODECOURSE_TITLESTUDENT_ID 显示为 NULL,因为这些学生尚未注册或注册任何课程。

你还可以将 IDFIRST_NAMELAST_NAMEGENDER 列与其他方法的输出进行比较,以查看一切是否按预期工作。

何时使用 NOT INNOT EXISTSLEFT JOIN/IS NULL

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

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

结论

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

Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook