mysql删除子查询

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

DELETE FROM orders
WHERE order_id IN (
    SELECT order_id FROM order_details WHERE order_id > 1000 LIMIT 100
);

START TRANSACTION;
DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_details
);
COMMIT;

LOCK TABLES orders WRITE, order_details READ;
DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_details
);
UNLOCK TABLES;

-- 创建示例表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 插入示例数据
INSERT INTO orders (order_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO order_details (detail_id, order_id, product_name) VALUES
(1, 1, 'Product A'),
(2, 1, 'Product B'),
(3, 2, 'Product C');

-- 删除没有订单详情的订单
DELETE FROM orders
WHERE order_id NOT IN (
    SELECT order_id FROM order_details
);

-- 查看结果
SELECT * FROM orders;