TOP

SQL EXISTS 语句

YouLibreCalc for Excel logo

SQL EXISTS 说明

EXISTS 运算符用于检查子查询中是否存在任何记录。

如果子查询返回一条或多条记录,则 EXISTS 运算符将返回 TRUE。


EXISTS 语法

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition)

演示数据库

以下是 Northwind 数据库的“Products”(“产品”)表中的示例:

ProductIDProductNameSupplierIDCategoryIDUnitPrice
1Chais1110 boxes x 20 bags18.00
2Chang1124 - 12 oz bottles19.00
3Aniseed Syrup1212 - 550 ml bottles10.00
4Chef Anton's Cajun Seasoning2248 - 6 oz jars22.00
5Chef Anton's Gumbo Mix2236 boxes21.35

以及表“Suppliers”(“供应商”)中的示例:

SupplierIDSupplierNameContactNameAddressCityPostalCodeCountryPhone
1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonaEC1 4SDUK(171) 555-2222
2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA(100) 555-4822
3Grandma Kelly's HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA(313) 555-5735
4Tokyo TradersYoshi Nagase9-8 Sekimai Musashino-shiTokyo100Japan(03) 3555-5011
5Cooperativa de Quesos 'Las Cabras'Antonio del Valle SaavedraCalle del Rosal 4Oviedo33007Spain(98) 598 76 54

SQL EXISTS 示例

以下 SQL 查询返回 TRUE 并包含产品价格低于 20 的供应商列表:

Run SQLSELECT SupplierName 
FROM Suppliers 
WHERE EXISTS (SELECT ProductName 
              FROM Products 
              WHERE Products.SupplierID = Suppliers.supplierID 
                AND Price < 20)

另一个 SQL 语句返回 TRUE 并包含产品价格等于 22 的供应商列表:

Run SQLSELECT SupplierName 
FROM Suppliers 
WHERE EXISTS (SELECT ProductName 
              FROM Products 
              WHERE Products.SupplierID = Suppliers.supplierID 
                AND Price = 22)