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正版授權，或了解更多產品信息請點擊