SQL语法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相关行（上）

SQL Prompt根据数据库的对象名称、语法和代码片段自动进行检索，为用户提供合适的代码选择。自动脚本设置使代码简单易读--当开发者不大熟悉脚本时尤其有用。SQL Prompt安装即可使用，能大幅提高编码效率。此外，用户还可根据需要进行自定义，使之以预想的方式工作。

点击下载SQL Prompt免费版

在检查是否存在符合条件的相关行时，是否应该始终使用EXISTS而不是使用COUNT？前者是否真的提供“卓越的性能和可读性”。本文描述了使用EXISTS或COUNT查找相关行的上半部分内容~

SQL Prompt的内置“性能”代码分析规则之一PE013声明（措辞如下）：

一些程序员使用COUNT（*）来检查是否有符合某些条件的行……为了获得更好的性能和可读性，建议改用EXISTS（）或NOT EXISTS（）。

现在将其重写为“……具有出色的可读性，并且性能始终可比较 ，在某些复杂情况下可能会更好”。但是，出色的可读性本身是值得争取的。

查找相关行的多种方法

与大多数编程问题一样，有多个查询将返回正确的答案，并且找到相关的行也没有什么不同。对于我们的示例，假设一位客户希望为曾经从他们的商店购买价格超过500美元的商品的用户提供特殊的电子邮件促销。我们的要求仅仅是为WideWorldImporters示例数据库设计查询，该查询返回这些客户的名称和电子邮件地址的列表。

一位开发人员提出了一种将EXISTS与子查询一起使用的解决方案，另一位开发人员提出了将COUNT（*）与子查询一起使用的解决方案，而另一位开发人员则提出了在SELECT中仅使用JOIN和DISTINCT子句的解决方案。还有其他建议。它们都能给您正确的结果，但是哪一个是“最佳”或最合适的解决方案？

COUNT

让我们从COUNT(*)解决方案开始：

SELECT People.FullName, People.EmailAddress
FROM   Sales.Customers
        JOIN Application.People
         ON People.PersonID = Customers.PrimaryContactPersonID
WHERE  People.EmailAddress IS NOT NULL 
  AND  (SELECT COUNT(*)
        FROM   Sales.Orders
                  JOIN Sales.OrderLines
                        ON OrderLines.OrderID = Orders.OrderID
        WHERE  Orders.CustomerID = Customers.CustomerID
          AND  OrderLines.UnitPrice > 500) > 0;

SQL Prompt立即提醒我们可能的问题，在SELECT COUNT（*）下有一条绿色的弯曲的行，这违反了性能规则PE013，但是我们很快就会知道（您还会看到其他波浪线表示未遵守别名表，在本文中将忽略它们）。

我们的要求是返回注册了价格超过500的商品的任何购买者的姓名和电子邮件地址。但是，按照书面说明，查询的字面意思是“对于每位客户，计算他们下达该价格的订单数量超过500，如果超过0，请告诉我他们的详细信息。”

我得到的印象是，程序员正在解决与需求中所述问题稍有不同的问题。通常，您将使用这种形式的查询来查找在一定范围内（例如2-5个订单）已发出一定数量订单的客户，而不仅仅是检查是否存在任何订单。

EXISTS

这里是EXISTS解决方案：

SELECT People.FullName, People.EmailAddress
FROM  Sales.Customers
       JOIN Application.People
         ON People.PersonID = Customers.PrimaryContactPersonID
WHERE  People.EmailAddress IS NOT NULL 
  AND  EXISTS 
       (SELECT *
        FROM   Sales.Orders
                  JOIN Sales.OrderLines 
                    ON OrderLines.OrderID = Orders.OrderID
         WHERE  Orders.CustomerID = Customers.CustomerID
           AND  OrderLines.UnitPrice > 500);

使用EXISTS运算符时表示：“对于价格在500或更高的物料，在客户表的每一行中，甚至不存在一行？”这与所述要求完全匹配，因此更容易为下一个程序员阅读和理解。

DISTINCT和其他解决方案

当然，有更多方法可以解决此问题。可以使用IN运算符代替子查询：

AND CustomerId in (SELECT CustomerId from Sales.Orders...

该查询将返回相同的正确结果，但将触发另一个违反代码分析规则的行为，PE019-考虑使用EXISTS而不是IN。 由于可以测试多列，因此通常首选使用EXISTS。 另外，当子查询的源数据包含NULL值时，使用NOT IN将返回意外结果。

另一种选择是使用JOIN条件而不是子查询来获取Sales.Orders和OrderLines，然后在SELECT语句中添加DISTINCT子句，以删除单价大于500的已订购多个商品的客户的重复行：

SELECT DISTINCT People.FullName, People.EmailAddress
FROM   Sales.Customers
        JOIN Application.People
         ON People.PersonID = Customers.PrimaryContactPersonID
        JOIN Sales.Orders
         ON Orders.CustomerID = Customers.CustomerID
        JOIN Sales.OrderLines
          ON OrderLines.OrderID = Orders.OrderID
WHERE  People.EmailAddress IS NOT NULL 
  AND  OrderLines.UnitPrice > 500;

我已经看到很多人都这样解决问题，认为这是首选的解决方法。但是，它不能以简单的方式回答问题，并且DISTINCT经常使用代码气味，表明在结束删除重复项之前，已处理了比必要更多的行。

解决此问题的另一种方法是创建一个所有客户的临时表，然后删除不符合条件的订单的行。我想说的是，这是一种人为设计的“我能想到的最古怪的想法”的解决方案风格，但是我已经在生产代码中多次看到了它（甚至还不是我所见过最奇怪的解决方案）。

点击下载SQL Prompt免费版

EXISTS或COUNT（或其他）哪个更好？

每个查询都给出与输出相同的行集；他们都给出正确的答案。那么，我们如何选择最佳或最合适的解决方案呢？依次归结为可读性和性能。

可读性

我的指导原则是，SQL始终旨在尽可能接近真实的书面语言。无论出现什么问题，都应以最简单的基于集合的方式编写查询，以便其他人可以像普通的声明性句子一样阅读并理解它。在大多数情况下，此解决方案也将表现最佳。

当然，并非总是如此。有时，必须调整一个简单的查询才能适应不稳定的数据库设计。但是，在足够的情况下，它是最好的起点。之后的所有内容都会变成性能调整，以处理特殊情况。

该EXISTS操作是检查基于一些标准行存在的最自然的方式，在我们的例子中，它以最简洁的方式回答了这个问题，并读取最像的需求的语句。如果它在性能和可伸缩性方面能带来可观的回报，我将只选择一种不易读的替代解决方案。

性能

在这里，我们预先列出了候选解决方案。实际上，大多数程序员在找到适合他们的答案时都会停下来。如果不是最佳选择，他们会在性能测试过程中发现并进行调整。相反，我看到过分复杂的查询被辩护是因为这样做避免了程序员曾经遇到的一些过时的性能问题（例如在SQL Server 7.0或更早版本上）。

这就是诸如Prompt之类的代码分析工具的价值。如果COUNT查询恰巧是我的第一个解决方案，则prompt会立即提示我，使用EXISTS将是一个更具可读性且可能更快的选择。

图1

当然，作为一个勤奋的程序员，我现在要同时测试这两种软件，而不是依靠内置规则或我在Internet上阅读的东西的智慧。

对于诸如此类的任务，我建议执行两个快速测试：比较查询的版本和可行的选择，以其执行统计信息为基础，然后，如果需要，还可以选择其执行计划。请注意，您使用的数据集越真实，可能会出现更明显的差异。

本教程内容较多，分为上下两个部分，后半部分内容查看请点击下方链接~感兴趣的朋友可以继续关注我们哦，或者下载SQL Prompt试用版进行评估~

相关内容推荐：

SQL语法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相关行（下）

SQL Prompt使用教程>>>

想要购买SQL Prompt正版授权，或了解更多产品信息请点击