MySQL EXISTS运算符

时间:2019-11-20 08:52:06  来源:igfitidea点击:

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

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运算符示例

让我们举一些使用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运算符与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运算符测试子查询返回的行是否存在。