PostgreSQL 筆記 - 子查詢應用

接下來的範例，我會用下列這三個資料表作為說明基礎：

基本概念

Subquery（子查詢） 是嵌套在另一個 SQL 查詢中的查詢。它可以出現在 SELECT、FROM、WHERE 等多個子句中。

簡單範例

列出所有價格高於 'Toys' 部門最高價格的商品：

💡 執行順序：PostgreSQL 會先執行內層的 subquery，得到 'Toys' 部門的最高價格，再用這個值與外層查詢比較。

Subquery 的使用位置

Subquery 可以出現在以下位置：

Note：出現在 FROM 子句中的 subquery 必須有別名（alias）

實戰練習：計算平均價格的最大值

問題：計算每個 manufacturer 的平均價格，然後取出最大值

參考資料表 phones：

解決方案：

執行邏輯：

1. 內層查詢：按 manufacturer 分組，計算每組的平均價格
2. 外層查詢：從這些平均價格中找出最大值

Subquery 在 JOIN 中的應用

以下兩種寫法效果相同：

方法 1：使用 Subquery 的 JOIN

方法 2：傳統 JOIN + WHERE

💡 性能考量：在大多數情況下，方法 2 的性能會更好，因為減少了子查詢的複雜度。

進階應用範例

基於計算條件的查詢

列出包含價格重量比 > 50 的商品的訂單 ID：

基於平均值的條件查詢

列出價格高於平均價格的商品：

NOT IN 的使用與注意事項

列出不與任何價格小於 100 的商品同部門的商品：

Note：如果 subquery 返回的結果中包含 NULL 值，NOT IN 可能不會返回任何結果。建議使用 NOT EXISTS 或加上 AND department IS NOT NULL 條件。

更安全的寫法：

ALL 與 SOME 運算符

ALL：比較所有值

列出比 'Industrial' 部門所有商品都貴的商品：

SOME：比較部分值

列出比 'Industrial' 部門至少一個商品貴的商品：

💡 等價關係：

• > ALL 等同於 > MAX(...)
• > SOME 等同於 > MIN(...)

Correlated Subquery（關聯子查詢）

找出各部門價格最高的商品

執行邏輯：

1. 外層查詢遍歷每一行商品
2. 對每一行，內層查詢計算該商品所在部門的最高價格
3. 比較當前商品價格是否等於部門最高價格

計算每個商品的訂單數量

獨立 SELECT 語句

當 subquery 返回單一值時，可以單獨執行：

Note：此類 subquery 必須返回恰好一行一列的結果，否則會出錯。

參考資料

• SQL and PostgreSQL: The Complete Developer's Guide