子查询

子查询

子查询指一个查询语句嵌套在另一个查询语句内的查询。
在select语句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。

{
SELECT *
FROM products
WHERE unit_price > (
	SELECT unit_price
    FROM products
    WHERE product_id=3
)

---

SELECT DISTINCT cust_id
FROM Orders
WHERE order_num IN (
   SELECT order_num
    FROM OrderItems
    WHERE item_price >= 10 
)
}

IN运算符

{
SELECT *
FROM products
WHERE product_id NOT IN(
	SELECT  DISTINCT product_id
	FROM order_items
)-- 使用IN是因为这里product_id不唯一，不能用“=”   
}

子查询与连接

{
SELECT *
FROM clients
LEFT JOIN invoices
	USING(client_id)
WHERE invoice_id IS NULL
-- 与下方代码功能一致
SELECT *
FROM clients
WHERE client_id NOT IN(
	SELECT DISTINCT client_id
	FROM invoices
)

---

SELECT DISTINCT
	customer_id,
    first_name,
    last_name
FROM customers c
LEFT JOIN orders o
	USING(customer_id)
LEFT JOIN order_items oi
	USING(order_id)
LEFT JOIN products p
	USING(product_id)
WHERE p.product_id=3
--与下方代码功能一致
SELECT 
	customer_id,
    first_name,
    last_name
FROM customers c
WHERE customer_id IN
(
	SELECT customer_id
	FROM orders
	WHERE order_id IN (
		SELECT order_id
		FROM order_items
		WHERE product_id=3
)
)
}

ALL

{
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

---

SELECT *
FROM invoices
WHERE invoice_total > ALL (
	SELECT invoice_total
    FROM invoices
    WHERE client_id = 3-- 注意这里返回值是多个
)
}

ANY

=ANY和IN等价

相关子查询

{
SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)-- 相关子查询会在主查询的每一行执行
}

EXISTS

{
SELECT *
FROM clients c
WHERE client_id IN (
    SELECT DISTINCT client_id
    FROM invoices
)

---

SELECT *
FROM clients c
WHERE EXISTS (
    SELECT client_id
    FROM invoices
    WHERE client_id=c.client_id
)-- EXISTS检查是否有符合条件的行，并没有把结果真的返回，而是逻辑TRUE，比IN更有效率
}

SELECT中的子查询

{
SELECT 
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
	FROM invoices
    ) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

---

SELECT
    client_id,
    name,
    (SELECT SUM(invoice_total)
		FROM invoices
		WHERE client_id=c.client_id) AS total_sales,
    (SELECT AVG(invoice_total) FROM invoices) AS average,
    (SELECT total_sales - average) AS difference
FROM clients c
}

FROM中的子查询

{
SELECT *
FROM (	
	SELECT
		client_id,
		name,
		(SELECT SUM(invoice_total)
			FROM invoices
			WHERE client_id=c.client_id) AS total_sales,
		(SELECT AVG(invoice_total) FROM invoices) AS average,
		(SELECT total_sales - average) AS difference
	FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL
}