MySQL查询语句——子查询（3）# 含有EXISTS的子查询

含有EXISTS的子查询：

含有EXISTS的子查询特点：

• 含有EXISTS的子查询实际上不产生任何数据
• 在有返回行的情况下，子查询将返回【TRUE】，反之则返回【FALSE】
• 由于仅仅判断是否存在返回行，所以子查询的选择列表通常指定为（*）

含有EXISTS的子查询格式：

SELECT select_list
FROM table_source
WHERE [NOT] EXISTS (subquery)

实例：

• 依然使用上一篇的数据库，表内容如下

# Table Customers
+--------+----------+----------+
| CustID | CustName | City     |
+--------+----------+----------+
|      1 | Name_A   | BeiJing  |
|      2 | Name_B   | ShangHai |
|      3 | Name_C   | BeiJing  |
|      4 | Name_D   | BeiJing  |
+--------+----------+----------+


# Table OrderHeader
+--------+---------+---------------------+---------------------+
| CustID | OrderID | OrderDate           | ShipTo              |
+--------+---------+---------------------+---------------------+
|      1 |     110 | 2018-07-01 00:00:00 | FangShan , BeiJing  |
|      1 |     111 | 2018-07-01 00:00:00 | HaiDian , BeiJing   |
|      2 |     113 | 2018-07-02 00:00:00 | PuDong , ShangHai   |
|      3 |     114 | 2018-07-03 00:00:00 | ChangPing , BeiJing |
+--------+---------+---------------------+---------------------+

• 通过EXISTS子查询找出全部有订单的客户及其所在城市

# SQL 语句
select CustName, City
from Customers as C where EXISTS
(select * from OrderHeader as O where O.CustID = C.CustID);

# 结果
+----------+----------+
| CustName | City     |
+----------+----------+
| Name_A   | BeiJing  |
| Name_B   | ShangHai |
| Name_C   | BeiJing  |
+----------+----------+

• 通常情况下，含有EXISTS的子查询与含有IN的子查询可以进行互相转换，例如上面的语句可以转换为：

# SQL语句
select CustName, City
from Customers
where CustID in
(select CustID from OrderHeader);

# 结果
+----------+----------+
| CustName | City     |
+----------+----------+
| Name_A   | BeiJing  |
| Name_B   | ShangHai |
| Name_C   | BeiJing  |
+----------+----------+

• 又如，上一篇我们提到的含有IN的子查询也可以转换为含有EXISTS形式的

# 含有 IN 的子查询
select OrderID, OrderDate, ShipTo
from OrderHeader
where CustID in
(select CustID from Customers where City = 'BeiJing');

# 含有 EXISTS 的子查询
select OrderID, OrderDate, ShipTo
from OrderHeader as O
where exists
(select * from Customers as C where C.CustID = O.CustID && C.City = 'BeiJing');