認識 SQL 子查詢（Subquery）

在資料庫查詢中，子查詢（Subquery）是一個非常有用的技術，可以幫助我們編寫更複雜的查詢，並從多個表中擷取所需的資料。在本文中，會以MySQL方法為主，介紹什麼是子查詢，以及如何在SQL查詢中使用它。

什麼是子查詢？

子查詢是一個SQL查詢，它嵌套在另一個查詢中。子查詢可以返回一個值、一個列或一個資料集，並且可以用於WHERE、FROM、SELECT、JOIN等子句中。子查詢通常用於從多個表中擷取資料，或者在查詢中使用聚合函數。

如何使用子查詢？

以下是一個簡單的例子，展示如何在SQL查詢中使用子查詢：

1
2
3

SELECT column1, column2
FROM table1
WHERE column1 = (SELECT column1 FROM table2 WHERE column2 = 'value');

在這個例子中，我們在table1中查詢column1和column2的值，並使用子查詢從table2中擷取column1的值。子查詢的結果將用於WHERE子句中，以過濾table1的結果。

子查詢的優點

子查詢有以下幾個優點：

提高查詢的靈活性：子查詢可以幫助我們從多個表中擷取所需的資料，提高查詢的靈活性。
簡化複雜查詢：子查詢可以幫助我們簡化複雜的查詢，使查詢更容易閱讀和維護。
提高查詢的效率：子查詢可以幫助我們從多個表中擷取所需的資料，提高查詢的效率。

子查詢的缺點

子查詢也有一些缺點：

可讀性差：過度使用子查詢會使查詢變得難以閱讀和維護。
效能問題：子查詢的效能通常比JOIN操作差，因為子查詢需要執行多次，而JOIN操作只需要執行一次。
結果不符合要求：子查詢的結果必須符合父查詢的要求，否則會出現錯誤。

子查詢的類型

子查詢可以分為以下幾種類型：

單行子查詢：子查詢返回單個值，通常用於WHERE子句中。
多行子查詢：子查詢返回多個值，通常用於IN、ANY、ALL等子句中。
多列子查詢：子查詢返回多個列，與多列相似，但是返回的是多個列而不是單一多個值。

子查詢的各類用法

單行子查詢

範例：
假設我有會員表與點數表，我想取得點數最多的會員姓名，可以使用子查詢。
1
2
3
SELECT name
FROM members
WHERE id = (SELECT member_id FROM points ORDER BY points DESC LIMIT 1);
- 說明：
  1. 先從points表中取得點數最多的會員ID。
  2. 再從members表中取得點數最多的會員姓名。

多行子查詢

範例：
假設我有會員表與點數表，我想取得點數大於100的會員姓名，可以使用子查詢。
1
2
3
SELECT name
FROM members
WHERE id IN (SELECT member_id FROM points WHERE points > 100);
- 說明：
  1. 先從points表中取得點數大於100的會員ID。
  2. 再從members表中取得點數大於100的會員姓名。

多列子查詢

範例：
假設我有會員表與點數表，我想取得點數大於100的會員姓名與點數，可以使用子查詢。
1
2
3
SELECT name, points
FROM members
WHERE (id, points) IN (SELECT member_id, points FROM points WHERE points > 100);
- 說明：
  1. 先從points表中取得點數大於100的會員ID與點數。
  2. 再從members表中取得點數大於100的會員姓名與點數。

搭配JOIN使用

大多狀況其實不太會合併使用，因為幾乎都可以只用JOIN或是分開使用達成目的。

範例1：
假設我有會員表與點數表，點數表有紀錄會員ID與更新時間，我想取得每個會員最新的點數，可以使用子查詢搭配JOIN操作。

SELECT members.*, subquery.points, subquery.updated_at
FROM members
LEFT JOIN (
  SELECT member_id, MAX(updated_at) AS updated_at
  FROM points
  GROUP BY member_id
) AS subquery
ON members.id = subquery.member_id;

說明：
1. 先從points表中取得每個會員最新的更新時間。
2. 再將points表與members表進行JOIN操作，取得每個會員最新的點數。
3. 最後取得members表的所有欄位、subquery的點數與更新時間。

以上寫法也可以單純使用JOIN操作，看起來更簡潔，所以其實可以根據需求來選擇使用哪種寫法。

SELECT members.*, points.points
FROM members
LEFT JOIN points
ON members.id = points.member_id
GROUP BY members.id
HAVING points.updated_at = MAX(points.updated_at);

說明：
1. 先將members表與points表進行JOIN操作，取得每個會員的點數與更新時間。
2. 再使用GROUP BY與HAVING取得每個會員最新的點數。

範例2：
假設我有產品與產品細項表，產品細項表中有紀錄產品ID和庫存數量，我想取得每個產品的庫存數量，並且依照大到小排列，可以使用子查詢搭配JOIN操作。

SELECT products.*, subquery.total_amount
FROM products
LEFT JOIN (
  SELECT product_id, SUM(amount) AS total_amount
  FROM product_details
  GROUP BY product_id
) AS subquery
ON products.id = subquery.product_id
ORDER BY total_amount DESC;

說明：
1. 先從product_details表中取得每個產品的庫存數量。
2. 再將product_details表與products表進行JOIN操作，取得每個產品的庫存數量。
3. 最後取得products表的所有欄位、subquery的庫存數量。

以上寫法也可以單純使用JOIN操作，看起來更簡潔，所以其實可以根據需求來選擇使用哪種寫法。

SELECT products.*, SUM(product_details.amount) AS total_amount
FROM products
LEFT JOIN product_details
ON products.id = product_details.product_id
GROUP BY products.id
ORDER BY total_amount DESC;

說明：
1. 先將products表與product_details表進行JOIN操作，取得每個產品的庫存數量。
2. 再使用GROUP BY取得每個產品的庫存總數。
3. 最後依照庫存總數由大到小排列。

結語

子查詢是一個蠻好用的技巧，個人也是蠻常使用，雖然大多情況下都可以使用JOIN完成，但有些情況下使用子查詢會比較簡潔、好閱讀，不過其實以效能考量的話，如果能用JOIN解決還是最好的選擇。