MySQL - MINUS 运算符

MySQL：模拟 `MINUS` 运算符

在标准 SQL 中，UNION、INTERSECT 和 MINUS（或某些方言如 PostgreSQL 和 SQL Server 中的 EXCEPT）等集合运算符用于将两个 SELECT 语句的结果组合成一个单一的结果集。MINUS 运算符用于返回第一个查询中存在但第二个查询中不存在的所有唯一行。

重要提示： MySQL 不支持 MINUS 或 EXCEPT 运算符。但是，您可以使用 LEFT JOIN 实现完全相同的效果。

概念理解：`MINUS` 的作用

想象您有两个列表（两个查询的结果）。Query1 MINUS Query2 将只返回出现在第一个列表但不在第二个列表中的项。

      Query1 Results            Query2 Results
+-----------------------+     +-----------------------+
|         Rows          |     |         Rows          |
|   A, B, C             |     |   B, D                |
+-----------------------+     +-----------------------+

Query1 MINUS Query2  =>  A, C

为了使 MINUS 操作生效，SELECT 语句必须具有相同数量的列且数据类型兼容。

MySQL 解决方案：带有 `NULL` 检查的 `LEFT JOIN`

在 MySQL 中模拟 MINUS 运算符的标准且最有效的方法是使用 LEFT JOIN 结合检查 NULL 值的 WHERE 子句。这种模式有效地过滤出左表中没有在右表中找到匹配的行。

语法

SELECT
    table1.column_to_return
FROM
    table1
LEFT JOIN
    table2 ON table1.matching_column = table2.matching_column
WHERE
    table2.matching_column IS NULL;

示例：查找没有订单的客户

让我们使用经典的 customers 和 orders 表。我们想找到所有从未下过订单的客户。这是 MINUS 操作的完美用例。

-- Setup Tables
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE orders (order_id INT, customer_id INT, amount DECIMAL(10,2));

INSERT INTO customers VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'), (4, 'David');
INSERT INTO orders VALUES (101, 1, 50.0), (102, 3, 120.0);

从概念上讲，我们想执行的操作是：(SELECT id FROM customers) MINUS (SELECT customer_id FROM orders)。

以下是我们如何在 MySQL 中实现它：

SELECT
    c.id,
    c.name
FROM
    customers AS c
LEFT JOIN
    orders AS o ON c.id = o.customer_id
WHERE
    o.customer_id IS NULL;

工作原理

LEFT JOIN 从 customers 表中的所有行开始。
它然后尝试在 orders 表中找到匹配的 customer_id。
如果找到匹配项（对于 Alice 和 Charlie），则 orders 中的列将填充数据。
如果未找到匹配项（对于 Bob 和 David），则 orders 中的列将填充 NULL。
WHERE o.customer_id IS NULL 子句随后过滤结果集，只保留未找到匹配的那些行。

输出

id	name
2	Bob
4	David

替代方法（以及为什么 `LEFT JOIN` 通常更好）

虽然 LEFT JOIN 是规范的解决方案，但您也可能会看到其他模式，如 NOT IN 或 NOT EXISTS。

使用 `NOT IN`

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT DISTINCT customer_id FROM orders WHERE customer_id IS NOT NULL);

注意： 如果子查询返回任何 NULL 值，NOT IN 可能会出现意外行为。与 JOIN 相比，它在大型数据集上的性能也可能较差。

使用 `NOT EXISTS`

SELECT id, name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

注意： NOT EXISTS 通常与 LEFT JOIN 的性能相当，并且一些开发者认为它对于此特定任务更具可读性。两者通常都被认为优于 NOT IN。

在应用程序代码中实现

由于解决方案是一个标准的 SELECT 查询，因此在应用程序中实现它非常简单。以下 Node.js 示例演示了如何查找一个部门中不在另一个部门的员工。

Node.js 示例

const mysql = require('mysql2/promise');

// 假设我们有两个表：'engineering_staff' 和 'on_call_roster'
// 我们想找到不在值班名册上的工程师。

async function findEngineersNotOnCall() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost', user: 'root', password: 'your_password', database: 'your_database'
        });

        const sql = `
            SELECT e.employee_id, e.name
            FROM engineering_staff AS e
            LEFT JOIN on_call_roster AS o ON e.employee_id = o.employee_id
            WHERE o.employee_id IS NULL;
        `;

        const [engineers] = await connection.execute(sql);

        console.log('目前不在值班名册上的工程师:');
        if (engineers.length > 0) {
            engineers.forEach(eng => {
                console.log(` - ID: ${eng.employee_id}, Name: ${eng.name}`);
            });
        } else {
            console.log('所有工程师都在名册上。');
        }

    } catch (error) {
        console.error('数据库查询失败:', error);
    } finally {
        if (connection) await connection.end();
    }
}

// 假设表已预先填充。
findEngineersNotOnCall();

MySQL - MINUS 运算符

MySQL：模拟 MINUS 运算符

概念理解：MINUS 的作用

MySQL 解决方案：带有 NULL 检查的 LEFT JOIN

语法