MySQL EXISTS

摘要：在本教程中，您将学习如何使用 MySQL EXISTS运算符以及何时使用它来提高查询性能。

MySQL EXISTS运算符简介

EXISTS运算符是一个布尔运算符，返回 true 或 false。 EXISTS运算符通常用于测试子查询返回的行是否存在。

下面说明了EXISTS运算符的基本语法：

SELECT 
    select_list
FROM
    a_table
WHERE
    [NOT] EXISTS(subquery);Code language: SQL (Structured Query Language) (sql)

如果子查询至少返回一行，则EXISTS运算符返回 true，否则返回 false。

此外， EXISTS运算符一旦找到匹配的行，就会立即终止进一步的处理，这有助于提高查询的性能。

NOT运算符对EXISTS运算符取反。换句话说，如果子查询没有返回行，则NOT EXISTS返回 true，否则返回 false。

请注意，您可以在子查询中使用SELECT * 、 SELECT column 、 SELECT a_constant或任何内容。结果是相同的，因为 MySQL 忽略了SELECT子句中出现的选择列表。

MySQL EXISTS运算符示例

让我们举一些使用EXISTS运算符的示例来了解它的工作原理。

MySQL SELECT EXISTS示例

考虑示例数据库中的以下customers和orders表。

以下语句使用EXISTS运算符来查找至少拥有一个订单的客户：

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS(
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber 
		= customers.customernumber);Code language: SQL (Structured Query Language) (sql)

试试看

在此示例中，对于customers表中的每一行，查询都会检查orders表中的customerNumber 。如果customers表中出现的customerNumber存在于orders表中，则子查询将返回第一个匹配行。因此， EXISTS运算符返回 true 并停止检查orders表。否则，子查询不返回任何行，并且EXISTS运算符返回 false。

以下示例使用NOT EXISTS运算符查找没有任何订单的客户：

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    NOT EXISTS( 
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber
	);Code language: SQL (Structured Query Language) (sql)

试试看

MySQL UPDATE EXISTS示例

假设您必须更新在旧金山办公室工作的员工的电话分机。

以下语句查找在San Franciso办公室工作的员工：

SELECT 
    employeenumber, 
    firstname, 
    lastname, 
    extension
FROM
    employees
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco' AND 
           offices.officeCode = employees.officeCode);Code language: SQL (Structured Query Language) (sql)

试试看

此示例将号码 1 添加到在旧金山办公室工作的员工的电话分机号：

UPDATE employees 
SET 
    extension = CONCAT(extension, '1')
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco'
                AND offices.officeCode = employees.officeCode);Code language: SQL (Structured Query Language) (sql)

怎么运行的。

• 首先， WHERE子句中的EXISTS运算符仅获取在旧金山办公室工作的员工。
• 其次， CONCAT()函数将电话分机与号码 1 连接起来。

MySQL INSERT EXISTS示例

假设您要将没有任何销售订单的客户存档在单独的表中。为此，您可以使用以下步骤：

首先，通过复制客户表的结构来创建一个用于归档customers的新表：

CREATE TABLE customers_archive 
LIKE customers;Code language: SQL (Structured Query Language) (sql)

其次，使用以下INSERT语句将没有任何销售订单的客户插入到customers_archive表中。

INSERT INTO customers_archive
SELECT * 
FROM customers
WHERE NOT EXISTS( 
   SELECT 1
   FROM
       orders
   WHERE
       orders.customernumber = customers.customernumber
);Code language: SQL (Structured Query Language) (sql)

试试看

第三，从customers_archive表中查询数据来验证插入操作。

SELECT * FROM customers_archive;Code language: SQL (Structured Query Language) (sql)

试试看

MySQL DELETE EXISTS示例

归档客户数据的最后一项任务是从customers表中删除customers_archive表中存在的客户。

为此，您可以在DELETE语句的WHERE子句中使用EXISTS运算符，如下所示：

DELETE FROM customers
WHERE EXISTS( 
    SELECT 
        1
    FROM
        customers_archive a
    
    WHERE
        a.customernumber = customers.customerNumber);Code language: SQL (Structured Query Language) (sql)

MySQL EXISTS运算符与IN运算符

要查找至少下过一份订单的客户，您可以使用IN运算符，如以下查询所示：

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    customerNumber IN (
        SELECT 
            customerNumber
        FROM
            orders);Code language: SQL (Structured Query Language) (sql)

试试看

让我们通过EXPLAIN语句来比较使用IN运算符的查询和使用EXISTS运算符的查询。

EXPLAIN SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber);Code language: SQL (Structured Query Language) (sql)

现在，检查使用IN运算符的查询的性能。

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN (SELECT 
            customerNumber
        FROM
            orders);Code language: SQL (Structured Query Language) (sql)

试试看

使用EXISTS运算符的查询比使用IN运算符的查询快得多。

原因是EXISTS运算符基于“至少找到”原则工作。当找到匹配的行时， EXISTS停止扫描表。

另一方面，当IN运算符与子查询组合时，MySQL 必须先处理子查询，然后使用子查询的结果来处理整个查询。

一般的经验法则是，如果子查询包含大量数据，则EXISTS运算符可提供更好的性能。

但是，如果子查询返回的结果集非常小，则使用IN运算符的查询执行速度会更快。

例如，以下语句使用IN运算符选择在旧金山办公室工作的所有员工。

SELECT 
    employeenumber, 
    firstname, 
    lastname
FROM
    employees
WHERE
    officeCode IN (SELECT 
            officeCode
        FROM
            offices
        WHERE
            offices.city = 'San Francisco');Code language: SQL (Structured Query Language) (sql)

试试看

让我们检查一下查询的性能。

它比我们在第一个示例中提到的使用EXISTS运算符的查询要快一点。请参阅下面使用EXIST运算符的查询的性能：

在本教程中，您学习了如何使用 MySQL EXISTS运算符来测试子查询返回的行是否存在。