EXISTS 是 SQL 中一个非常有用的关键字,用于检查子查询是否返回任何行。它在验证两个表之间是否存在对应关系时特别有用。
SELECT column_name(s)
FROM table1
WHERE EXISTS (SELECT column_name FROM table2 WHERE condition);
SELECT customer_name
FROM customers
WHERE EXISTS (
SELECT 1
FROM orders
WHERE orders.customer_id = customers.customer_id
);
SELECT product_name
FROM products
WHERE EXISTS (
SELECT 1
FROM inventory
WHERE inventory.product_id = products.product_id
AND quantity > 0
);
NOT EXISTS 用于检查不存在对应关系的情况:
-- 查找没有订单的客户
SELECT customer_name
FROM customers
WHERE NOT EXISTS (
SELECT 1
FROM orders
WHERE orders.customer_id = customers.customer_id
);
对于大数据集,EXISTS 通常比 IN 更高效。
-- 查找有高价值订单(>1000)的客户
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_amount > 1000
);
EXISTS 是验证表间关系的高效工具,特别适合只需要知道是否存在而不需要具体数据的场景。