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