SQL語法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相關(guān)行（上）

SQL Prompt根據(jù)數(shù)據(jù)庫(kù)的對(duì)象名稱、語法和代碼片段自動(dòng)進(jìn)行檢索，為用戶提供合適的代碼選擇。自動(dòng)腳本設(shè)置使代碼簡(jiǎn)單易讀--當(dāng)開發(fā)者不大熟悉腳本時(shí)尤其有用。SQL Prompt安裝即可使用，能大幅提高編碼效率。此外，用戶還可根據(jù)需要進(jìn)行自定義，使之以預(yù)想的方式工作。

點(diǎn)擊下載SQL Prompt免費(fèi)版

在檢查是否存在符合條件的相關(guān)行時(shí)，是否應(yīng)該始終使用EXISTS而不是使用COUNT？前者是否真的提供“卓越的性能和可讀性”。本文描述了使用EXISTS或COUNT查找相關(guān)行的上半部分內(nèi)容~

SQL Prompt的內(nèi)置“性能”代碼分析規(guī)則之一PE013聲明（措辭如下）：

一些程序員使用COUNT（*）來檢查是否有符合某些條件的行……為了獲得更好的性能和可讀性，建議改用EXISTS（）或NOT EXISTS（）。

現(xiàn)在將其重寫為“……具有出色的可讀性，并且性能始終可比較 ，在某些復(fù)雜情況下可能會(huì)更好”。但是，出色的可讀性本身是值得爭(zhēng)取的。

查找相關(guān)行的多種方法

與大多數(shù)編程問題一樣，有多個(gè)查詢將返回正確的答案，并且找到相關(guān)的行也沒有什么不同。對(duì)于我們的示例，假設(shè)一位客戶希望為曾經(jīng)從他們的商店購(gòu)買價(jià)格超過500美元的商品的用戶提供特殊的電子郵件促銷。我們的要求僅僅是為WideWorldImporters示例數(shù)據(jù)庫(kù)設(shè)計(jì)查詢，該查詢返回這些客戶的名稱和電子郵件地址的列表。

一位開發(fā)人員提出了一種將EXISTS與子查詢一起使用的解決方案，另一位開發(fā)人員提出了將COUNT（*）與子查詢一起使用的解決方案，而另一位開發(fā)人員則提出了在SELECT中僅使用JOIN和DISTINCT子句的解決方案。還有其他建議。它們都能給您正確的結(jié)果，但是哪一個(gè)是“最佳”或最合適的解決方案？

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（*）下有一條綠色的彎曲的行，這違反了性能規(guī)則PE013，但是我們很快就會(huì)知道（您還會(huì)看到其他波浪線表示未遵守別名表，在本文中將忽略它們）。

我們的要求是返回注冊(cè)了價(jià)格超過500的商品的任何購(gòu)買者的姓名和電子郵件地址。但是，按照書面說明，查詢的字面意思是“對(duì)于每位客戶，計(jì)算他們下達(dá)該價(jià)格的訂單數(shù)量超過500，如果超過0，請(qǐng)告訴我他們的詳細(xì)信息。”

我得到的印象是，程序員正在解決與需求中所述問題稍有不同的問題。通常，您將使用這種形式的查詢來查找在一定范圍內(nèi)（例如2-5個(gè)訂單）已發(fā)出一定數(shù)量訂單的客戶，而不僅僅是檢查是否存在任何訂單。

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運(yùn)算符時(shí)表示：“對(duì)于價(jià)格在500或更高的物料，在客戶表的每一行中，甚至不存在一行？”這與所述要求完全匹配，因此更容易為下一個(gè)程序員閱讀和理解。

DISTINCT和其他解決方案

當(dāng)然，有更多方法可以解決此問題。可以使用IN運(yùn)算符代替子查詢：

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

該查詢將返回相同的正確結(jié)果，但將觸發(fā)另一個(gè)違反代碼分析規(guī)則的行為，PE019-考慮使用EXISTS而不是IN。 由于可以測(cè)試多列，因此通常首選使用EXISTS。 另外，當(dāng)子查詢的源數(shù)據(jù)包含NULL值時(shí)，使用NOT IN將返回意外結(jié)果。

另一種選擇是使用JOIN條件而不是子查詢來獲取Sales.Orders和OrderLines，然后在SELECT語句中添加DISTINCT子句，以刪除單價(jià)大于500的已訂購(gòu)多個(gè)商品的客戶的重復(fù)行：

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;

我已經(jīng)看到很多人都這樣解決問題，認(rèn)為這是首選的解決方法。但是，它不能以簡(jiǎn)單的方式回答問題，并且DISTINCT經(jīng)常使用代碼氣味，表明在結(jié)束刪除重復(fù)項(xiàng)之前，已處理了比必要更多的行。

解決此問題的另一種方法是創(chuàng)建一個(gè)所有客戶的臨時(shí)表，然后刪除不符合條件的訂單的行。我想說的是，這是一種人為設(shè)計(jì)的“我能想到的最古怪的想法”的解決方案風(fēng)格，但是我已經(jīng)在生產(chǎn)代碼中多次看到了它（甚至還不是我所見過最奇怪的解決方案）。

點(diǎn)擊下載SQL Prompt免費(fèi)版

EXISTS或COUNT（或其他）哪個(gè)更好？

每個(gè)查詢都給出與輸出相同的行集；他們都給出正確的答案。那么，我們?nèi)绾芜x擇最佳或最合適的解決方案呢？依次歸結(jié)為可讀性和性能。

可讀性

我的指導(dǎo)原則是，SQL始終旨在盡可能接近真實(shí)的書面語言。無論出現(xiàn)什么問題，都應(yīng)以最簡(jiǎn)單的基于集合的方式編寫查詢，以便其他人可以像普通的聲明性句子一樣閱讀并理解它。在大多數(shù)情況下，此解決方案也將表現(xiàn)最佳。

當(dāng)然，并非總是如此。有時(shí)，必須調(diào)整一個(gè)簡(jiǎn)單的查詢才能適應(yīng)不穩(wěn)定的數(shù)據(jù)庫(kù)設(shè)計(jì)。但是，在足夠的情況下，它是最好的起點(diǎn)。之后的所有內(nèi)容都會(huì)變成性能調(diào)整，以處理特殊情況。

該EXISTS操作是檢查基于一些標(biāo)準(zhǔn)行存在的最自然的方式，在我們的例子中，它以最簡(jiǎn)潔的方式回答了這個(gè)問題，并讀取最像的需求的語句。如果它在性能和可伸縮性方面能帶來可觀的回報(bào)，我將只選擇一種不易讀的替代解決方案。

性能

在這里，我們預(yù)先列出了候選解決方案。實(shí)際上，大多數(shù)程序員在找到適合他們的答案時(shí)都會(huì)停下來。如果不是最佳選擇，他們會(huì)在性能測(cè)試過程中發(fā)現(xiàn)并進(jìn)行調(diào)整。相反，我看到過分復(fù)雜的查詢被辯護(hù)是因?yàn)檫@樣做避免了程序員曾經(jīng)遇到的一些過時(shí)的性能問題（例如在SQL Server 7.0或更早版本上）。

這就是諸如Prompt之類的代碼分析工具的價(jià)值。如果COUNT查詢恰巧是我的第一個(gè)解決方案，則prompt會(huì)立即提示我，使用EXISTS將是一個(gè)更具可讀性且可能更快的選擇。

圖1

當(dāng)然，作為一個(gè)勤奮的程序員，我現(xiàn)在要同時(shí)測(cè)試這兩種軟件，而不是依靠?jī)?nèi)置規(guī)則或我在Internet上閱讀的東西的智慧。

對(duì)于諸如此類的任務(wù)，我建議執(zhí)行兩個(gè)快速測(cè)試：比較查詢的版本和可行的選擇，以其執(zhí)行統(tǒng)計(jì)信息為基礎(chǔ)，然后，如果需要，還可以選擇其執(zhí)行計(jì)劃。請(qǐng)注意，您使用的數(shù)據(jù)集越真實(shí)，可能會(huì)出現(xiàn)更明顯的差異。

本教程內(nèi)容較多，分為上下兩個(gè)部分，后半部分內(nèi)容查看請(qǐng)點(diǎn)擊下方鏈接~感興趣的朋友可以繼續(xù)關(guān)注我們哦，或者下載SQL Prompt試用版進(jìn)行評(píng)估~

相關(guān)內(nèi)容推薦：

SQL語法提示工具SQL Prompt教程：使用EXISTS或COUNT查找相關(guān)行（下）

SQL Prompt使用教程>>>

想要購(gòu)買SQL Prompt正版授權(quán)，或了解更多產(chǎn)品信息請(qǐng)點(diǎn)擊“咨詢?cè)诰€客服”