MySQL EXISTS运算符示例

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

MySQL SELECT EXISTS示例

请考虑以下示例数据库中的客户和订单表。

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

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS(
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber 
		= customers.customernumber);

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

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

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    NOT EXISTS( 
	SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber
	);

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);

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

UPDATE employees 
SET 
    extension = CONCAT(extension, '1')
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            offices
        WHERE
            city = 'San Francisco'
                AND offices.officeCode = employees.officeCode);

这个怎么运作。

• 首先，WHERE子句中的EXISTS运算符仅获得在旧金山办公室工作的员工。

• 其次，CONCAT()函数将电话分机号与数字1连接在一起。

MySQL INSERT EXISTS示例

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

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

CREATE TABLE customers_archive 
LIKE customers;

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

INSERT INTO customers_archive
SELECT * 
FROM customers
WHERE NOT EXISTS( 
   SELECT 1
   FROM
       orders
   WHERE
       orders.customernumber = customers.customernumber
);

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

SELECT * FROM customers_archive;

MySQL DELETE EXISTS示例

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

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

DELETE FROM customers
WHERE EXISTS( 
    SELECT 
        1
    FROM
        customers_archive a
    
    WHERE
        a.customernumber = customers.customerNumber);

MySQL EXISTS运算符简介

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

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

SELECT 
    select_list
FROM
    a_table
WHERE
    [NOT] EXISTS(subquery);

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

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

NOT运算符使EXISTS运算符无效。
换句话说，如果子查询不返回任何行，则NOT EXISTS返回true，否则返回false。

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

MySQL EXISTS运算符与IN运算符

要查找至少下了一个订单的客户，可以使用IN操作符，如以下查询所示：

SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    customerNumber IN (
        SELECT 
            customerNumber
        FROM
            orders);

让我们通过使用EXPLAIN语句，将使用IN运算符的查询与使用EXISTS运算符的查询进行比较。

EXPLAIN SELECT 
    customerNumber, 
    customerName
FROM
    customers
WHERE
    EXISTS( 
        SELECT 
            1
        FROM
            orders
        WHERE
            orders.customernumber = customers.customernumber);

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

SELECT 
    customerNumber, customerName
FROM
    customers
WHERE
    customerNumber IN (SELECT 
            customerNumber
        FROM
            orders);

使用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');

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

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

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

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