深入淺出 SQL 的子查詢 Subquery 各項常用技巧

帶你直觀理解 SQL 的子查詢 Subquery 技巧與時機

學．誌｜Chris Kang

Follow

6 min readFeb 22, 2021

--

Photo by Stephen Phillips — Hostreviews.co.uk on Unsplash

在學習進階的 SQL 查詢技巧時，最困難的技巧我個人覺得莫過於各種子查詢的嵌套。練習時也不確定是否要使用 Subquery。因此筆者今天就帶讀者來理解常用的情境有哪些，又該如何操作。

When to use Subquery?

其實子查詢（Subquery）並沒有官方定義的使用時機，他更像是我們在使用 Python 時，習慣先對表格做一些處理來挑出篩選條件、轉換表格格式等。

因此在 SQL 中使用子查詢的時機，其實就和你在 Python 中做 ETL 的時候一樣，只要你認為表格或資料的呈現方式，並無法以原本的 Column 抽取出來，就可以考慮使用子查詢來突破困境。

本篇文章會提到 Subquery 常遇到的三種狀況，以及個別判斷與使用的方式。就讓我們先理解一下吧！下面會提到三種常見的 Subquery，放在不同 keyword 的簡單判斷時機。

WHERE｜用來篩選特別的條件，例如平均、中位數等。
FROM｜重塑表格的格式、或預先進行計算或濾掉資料。
JOIN｜進行預先計算或過濾資料。
SELECT｜快速計算單一數值，特別是多表格存取時。

How to write SQL in Proper way?

這裡也要帶到在撰寫 SQL 的習慣和流程。當我們在思考一筆資料可以如何被提取時，需要先盡量以紙筆寫下表格預期呈現的格式；接著再來拆分這個表格，思考能以不同的技巧來組合。

接著，如果有需要使用子查詢的技巧，會建議先從子查詢來開始撰寫。如果直接從總表來寫，容易導致對子查詢的格式不清晰，因此會時常發生來回修改表格的狀況。

其中子查詢的表格，也推薦使用之前在 十分鐘內快速上手與使用 Window function 寫到的公用表表達式（CTE），不僅能讓整個 SQL 的表格看起來清晰易懂，也能讓子查詢表格能夠被多次使用。因此有興趣的讀者，也可以到上面這篇文章中瞭解 CTE 使用時的細節。

這裡再一次展示 CTE 的寫法，所以下面的介紹全部都可以改寫成 CTE 的格式，端看讀者的使用情境與習慣來取捨。

“WHERE” Subquery Method

如果我們希望能藉由某些條件來篩選資料，我們就能將 Subquery Table 放在 WHERE 之後，來篩選我們想要的資料。這裡舉個常見的例子：

如何找到那些比整體平均身高還高的學生？

所以當我們能夠藉由一或多個單一條件來篩選原本的表格時，便能以 WHERE 的子查詢來解決問題，但如果條件太多，便可能要考慮 CASE 來避免 Table 的易讀性下滑。

當然，很多時候我們會面對到的問題不僅僅希望找到比平均身高還高的學生，我們還希望能找到在所有比平均高的學生中，每個人的 BMI。這時候就是 FROM Subquery 派上用場的時候。

“FROM” Subquery Method

當我們希望把表格變形（變短或長）或預先篩選表格時，就需要使用 FROM Subquery 才能達到我們的預期目的。

如何找到在比整體平均身高還高的學生中，BMI > 23 的學生？

因此當我們需要先對原先的表格進行預處理時，特別是這些表格使用的聚合函數（Aggregation function），使用 WHERE 就能先讓表格變成我們希望呈現的資料，再根據這些資料來進行調整或篩選。

當然上述的例子，其實可以直接整合成一個表；但這裡也想表達，SQL 查詢式沒有最正確的撰寫方式，只要符合效益的查詢方式都是好的查詢方式。

“JOIN” Method Subquery

如果我們遇到要先預處理或計算的資料，但並不想讓整個表格變得非常複雜，因為如果在顯示欄位多的情況下，純粹使用 FROM 會讓子表格變得非常的長。因此使用 JOIN Method，就能讓表格維持簡單而易懂。

“SELECT” Method Subquery

當我們想針對每筆資料計算特定數值時，SELECT 子查詢就派上用場了。這裡切記兩個細節：

SELECT 子查詢僅能用於單一數值，否則就會跳出 Error。
若 SELECT 子查詢有特定條件，則在子查詢和總表都要記得加上條件。

上面的例子比較簡單，實務上會遇到的則可能會有多個表格。如想計算使用者在不同類別的商品消費數量，就可以使用 SELECT 子查詢來快速計算每個類別的數量，而不用使用大量的 JOIN 來嵌套表格。

這裡借用 DataCamp 的 SELECT 子查詢範例來說明，可以發現透過這個方式，能快速計算出差分。

Summary in SQL Subquery

實際上在查詢時，我們是可能會用到超過一個以上的 Subquery；但也因此要注意 Subquery 其實會耗費額外的運算資源，因此如果能夠在單一表格內呈現，就盡量使用原有的函式或在同一張表查詢。

另一部份，也要注意查詢時是否在總表和子表的 filter 計算的範圍是否符合預期，否則可能會導致查詢出來的資料不精確。

這篇文章除了希望帶給讀者快速理解子查詢的情境，也是讓我自己能更釐清不同情境下，使用 Subquery 的方式與時機，希望對讀者有幫助。

You May Interest In:

｜參考資料｜