1.子查询与连接
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM table)
等于
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL
2.ALL用法
WHERE invoice_total > ALL(
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
等于
WHERE invoice_total >(
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
3.ANY用法
WHERE client_id IN ( IN 等于 =ANY
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >=2
)
4.相关子查询
SELECT *
FROM employees e
WHERE salary >(
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
)
每遍一次 e.office_id,重新查询一次 AVG(salary)
5.EXISTS用法
验证条件是否可行
SELECT *
FROM clients c
WHERE EXISTS (
SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)
子查询中为true时,结果返回
NOT EXISTS 结果为 false 时通过
6.SELECT中子查询
SELECT
(SELECT AVG(invoice_total) FROM invoices) AS average,
invoice_total - (SELECT average) AS difference
FROM invoices
7.FROM中子查询
SELECT *
FROM(
SELECT
(SELECT AVG(invoice_total) FROM invoices) AS average,
invoice_total - (SELECT average) AS difference
FROM invoices
)AS table
WHERE ..