子查询作为数据源
子查询生成的结果集包含行、列数据，因而非常适合将它与表一起包含在from子句的子查询里。例：
SELECT d.dept_id, d.name, e_cnt.how_many num_employees
FROM department d INNER JOIN
(SELECT dept_id, COUNT(*) how_many
FROM employee
GROUP BY dept_id) e_cnt
ON d.dept_id = e_cnt.dept_id;
数据加工
除了使用查询总结现有数据，读者还可以生成数据库中不存在的数据。例：
SELECT 'zifeiy' name, 0 low_limit, 4999.99 high_limit
UNION ALL
SELECT 'feiyzi' name, 5000 low_limit, 9999.99 high_limit
UNION ALL
SELECT 'hahahe' name, 10000 low_limit, 9999999.99 high_limit;
结果：
+--------+-----------+------------+
| name | low_limit | high_limit |
+--------+-----------+------------+
| zifeiy | 0 | 4999.99 |
| feiyzi | 5000 | 9999.99 |
| hahahe | 10000 | 9999999.99 |
+--------+-----------+------------+
3 rows in set (0.00 sec)
上面的SQL能够生成一个包含3组数据的结果集，然后我们将其滋味子查询添加到下面的SQL中：
SELECT groups.name, COUNT(*) num_customers
FROM
(SELECT SUM(a.avail_balance) cust_balance
FROM account a INNER JOIN product p
ON a.product_cd = p.product_cd
WHERE p.product_type_cd = 'ACCOUNT'
GROUP BY a.cust_id) cust_rollup
INNER JOIN
(SELECT 'zifeiy' name, 0 low_limit, 4999.99 high_limit
UNION ALL
SELECT 'feiyzi' name, 5000 low_limit, 9999.99 high_limit
UNION ALL
SELECT 'hahahe' name, 10000 low_limit, 9999999.99 high_limit) groups
ON cust_rollup.cust_balance
BETWEEN groups.low_limit AND groups.high_limit
GROUP BY groups.name;
过滤条件中的子查询
过滤条件中的子查询不一定出现在where子句中，下面的例子演示在having子句中使用子查询来查找开户最多的雇员：
SELECT open_emp_id, COUNT(*) how_many
FROM account
GROUP BY open_emp_id
HAVING COUNT(*) = (
SELECT MAX(emp_cnt.how_many)
FROM (
SELECT COUNT(*) how_many
FROM account
GROUP BY open_emp_id
) emp_cnt
);
子查询作为表达式生成器
单行单列的标量子查询，除了用于过滤条件中外，1还可以用在表达式可以出现的任何位置。其中包括查询中的select和order by子句以及insert语句中的values子句。例：
SELECT
(
SELECT p.name FROM product p
WHERE p.product_cd = a.product_cd
AND p.product_type_cd = 'ACCOUNT'
) product,
(
SELECT b.name FROM branch b
WHERE b.branch_id = a.open_branch_id
) branch,
(
SELECT CONCAT(e.fname, ' ', e.lname) FROM employee e
WHERE e.emp_id = a.open_emp_id
) name,
SUM(a.avail_balance) tot_deposits
FROM account a
GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id
ORDER BY 1,2;
此SQL在select子句中使用了子查询，它在select子句中使用了关联标量子查询查找产品、分行和雇员的名字。