嵌套查询

嵌套查询

• 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;