-- Customers 表
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50)
);
-- Orders 表
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
-- 插入一些示例数据
INSERT INTO Customers (customer_id, customer_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');
INSERT INTO Orders (order_id, customer_id, order_date) VALUES
(101, 1, '2023-08-01'),
(102, 2, '2023-08-02');
大概说明: A表中的xx ,以某个条件判定,没有在B表中出现,就符合not exists,就输出A表中的xx记录
SELECT *
FROM Customers c
WHERE NOT EXISTS (
SELECT *
FROM Orders o
WHERE o.customer_id = c.customer_id
);
它表示的是,对于Customers 表中每个客户,
通过 NOT EXISTS 子查询检查在 Orders 表中是否存在与该客户相关的订单记录
(条件 o.customer_id = c.customer_id)。
如果子查询找不到与该客户相关的订单记录,那么子查询为空,
此时 NOT EXISTS 条件为真,表示该客户没有下过订单。
就会查询输出该条记录