MySQL 中的减法运算
Mehvish Ashiq
2023年1月30日
2022年5月13日
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
操作。你可以根据自己的需要、舒适度和要求使用它。
Author: Mehvish Ashiq