mysql 子查询删除

DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_items
);

DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_items WHERE order_id > 1000
)
AND order_id > 1000;

START TRANSACTION;

DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_items
);

COMMIT;

LOCK TABLES orders WRITE, order_items READ;

DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_items
);

UNLOCK TABLES;

-- 创建示例表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department_id INT
);

-- 插入示例数据
INSERT INTO departments (department_id, department_name) VALUES (1, 'HR');
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (1, 'Alice', 1);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (2, 'Bob', NULL);

-- 删除没有部门信息的员工
DELETE FROM employees
WHERE department_id NOT IN (
    SELECT department_id FROM departments
);