EXISTS
指定一个子查询,检测行的存在。
语法
EXISTS subquery
参数
subquery
是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)
结果类型
Boolean
结果值
如果子查询包含行,则返回 TRUE. EXISTS(SELECT NULL)也返回True
SELECT c.CustomerID, CompanyName FROM Customers c
执行结果:91条记录
SELECT c.CustomerID, CompanyName
FROM Customers c
WHERE EXISTS(SELECT NULL)
执行结果:91条记录
找出所有下过订单的客户
使用EXISTS的写法:
SELECT c.CustomerID, CompanyName
FROM Customers c
WHERE EXISTS
(SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)
执行结果:89条记录
使用in的写法:
SELECT c.CustomerID, CompanyName
FROM Customers c
where c.CustomerID in(select distinct CustomerID from Orders)
执行结果:89条记录
检查:
SELECT c.CustomerID, CompanyName FROM Customers c
where c.CustomerID='ALFKI'
执行结果:1条记录
SELECT OrderID FROM Orders o
where o.CustomerID='ALFKI'
执行结果:6条记录
SELECT c.CustomerID, CompanyName
FROM Customers c
WHERE EXISTS
(SELECT OrderID FROM Orders o WHERE o.CustomerID = c.CustomerID) and c.CustomerID='ALFKI'
执行结果:1条记录
找出所有没下过订单的客户
使用NOT EXISTS的写法:
SELECT c.CustomerID, CompanyName
FROM Customers c
WHERE NOT EXISTS
(SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)
执行结果:2条记录
使用NOT IN的写法:
SELECT c.CustomerID, CompanyName
FROM Customers c
where c.CustomerID not in(select distinct CustomerID from Orders)
执行结果:2条记录