嵌套查询
- A common use of subqueries is to perform tests for
- set membership: in, not in
- set comparisons: some, all
- empty set: exists, not exists
- set containment: not exists (B except A)
- duplicate tuples: unique, not unique
Subqueries in the From Clause
Example:
select dept_name, avg_salary
from (select dept_name, avg (salary) as avg_salary
group by dept_name)
where avg_salary > 42000;
With Clause
The with clause provides a way of defining a temporary view whose definition is available only to the query in which the with clause occurs.
提供一个暂时的值供当前使用with的查询操作使用
Example:
with max_budget (value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget = max_budget.value;