Subquery

SQL： Hi my son.

子查詢可以套好幾層

放SELECT

用於傳回一個單一值或一列值,作為主查詢的一部分。

SELECT (SELECT COUNT(*) FROM orders WHERE customer_id = c.customer_id) as order_count FROM customers c;

放FROM

子查詢作為一個臨時表,可用於復雜的查詢中。

SELECT e.employee_name, e.salary FROM (SELECT * FROM employees WHERE department_id = 10) e WHERE e.salary > 5000;

放WHERE

用來對主查詢的記錄進行過濾。

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

放Join

子查詢作為JOIN條件的一部分,用於關聯多個表。

SELECT e.employee_name, d.department_name FROM employees e JOIN (SELECT department_id, department_name FROM departments WHERE location_id = 1) d ON e.department_id = d.department_id;

放Having

用於對分組後的資料進行過濾。

SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

放EXISTS 和 NOT EXISTS

用來檢查子查詢是否回傳任何記錄。

SELECT customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

放ANY 和 ALL

用來比較子查詢傳回的多個值。

SELECT product_name FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id = 2);

---

如果你覺得這篇文章很棒，請你不吝點讚 (ﾟ∀ﾟ)