sql-delete-joins

SQL - 使用 JOIN 删除数据

在关系型数据库中，数据通常分散在多个表中。一个常见任务是根据另一个表中的条件从一个表中删除记录。本教程涵盖了执行此类删除的现代且有效的技术。

挑战：删除相关数据

想象一个电子商务数据库，其中包含 customers 表和 orders 表。你如何删除来自特定区域客户所下的所有订单？你不能只运行 DELETE FROM orders，因为区域信息在 customers 表中。这就是使用 JOIN 或子查询进行删除变得至关重要的原因。

让我们设置示例表：

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    order_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO customers VALUES
(1, 'John Doe', 'New York'),
(2, 'Jane Smith', 'London'),
(3, 'Peter Jones', 'New York');

INSERT INTO orders VALUES
(101, 1, '2023-10-01', 150.00),
(102, 2, '2023-10-03', 200.50),
(103, 1, '2023-10-05', 75.25),
(104, 3, '2023-10-06', 300.00);

方法 1：使用子查询的 DELETE（标准 SQL）

实现此目的最便携和标准的方法是在 WHERE 子句中使用带有 IN 运算符的子查询。此方法适用于几乎所有 SQL 数据库。

语法

DELETE FROM table_to_delete
WHERE column_name IN (SELECT column_to_match FROM other_table WHERE condition);

示例：删除“纽约”客户的订单

我们想删除 orders 表中所有属于居住在“纽约”客户的记录。

DELETE FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York');

此查询首先找到“纽约”客户的所有 id（即 1 和 3），然后删除 customer_id 与之匹配的所有订单（订单 101、103 和 104）。

方法 2：使用 JOIN 语法的 DELETE（特定于供应商）

某些数据库系统在 DELETE 语句中提供了更直接的 JOIN 语法。这有时会更具可读性或性能，但它不是标准 SQL，并且语法各不相同。

PostgreSQL / SQL Server 语法

这些系统使用 USING 或 FROM 子句来引入连接的表。

-- PostgreSQL 语法
DELETE FROM orders o
USING customers c
WHERE o.customer_id = c.id AND c.city = 'New York';

MySQL 语法

MySQL 的语法略有不同，它在 FROM 子句之前指定要从哪个表中删除。

-- MySQL 语法
DELETE o
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.id
WHERE c.city = 'New York';

重要提示： 原始教程声称 DELETE...JOIN 可以同时从所有表中删除的说法通常是错误的且危险的。大多数语法只允许从 DELETE 子句中列出的一个表中删除。尽管某些方言（如 MySQL）有从多个表中删除的语法（DELETE c, o FROM ...），但它很少使用且可能令人困惑。主要用例是基于 JOIN 从一个表中删除。

方法 3：级联删除（声明式方法）

维护引用完整性的一种强大且由数据库强制执行的方法是 ON DELETE CASCADE 约束。当你定义外键时，你可以指示数据库在父记录被删除时自动删除任何子记录。

示例设置

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    -- 此外键将级联删除
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

有了这种结构，如果你删除一个客户，他们的所有订单都会被数据库自动删除。

-- 此单个命令将删除客户 1 及其所有相关订单（101 和 103）。
DELETE FROM customers WHERE id = 1;

**优点：** 在数据库层面强制执行数据完整性。简化应用程序逻辑。
**缺点：** 如果不完全理解级联效应，可能会很危险。它可能会触发跨多个表的级联删除。

常见错误与调试

**专家提示：删除前先查询！**

在运行破坏性的 `DELETE` 命令之前，始终编写一个具有完全相同的 JOIN 和 `WHERE` 子句的 `SELECT` 语句，以验证将影响哪些行。这是最重要的安全检查。

`-- 对于我们的示例，请先运行此查询：
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE city = 'New York');`

这将准确地显示即将被删除的内容，从而防止灾难性错误。