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