|
|
|
A subquery is a select statement nested within another select statement.
|
|
|
A subquery is a select statement nested within another select statement.
A subquery in a where clause can be used to qualify a column against a set of rows.
For example, the following subquery returns the department numbers for departments on the third floor. The outer query retrieves the names of employees who work on the third floor.
select ename
from employee
where dept in
(select dno
from dept
where floor = 3);
Subqueries often take the place of expressions in predicates. Subqueries can be used in place of expressions only in the specific instances outlined in the descriptions of Predicates in SQL.
The syntax of the subquery is identical to that of the subselect, except the select clause must contain only one element. A subquery can see correlation names defined (explicitly or implicitly) outside the subquery. For example:
select ename
from employee empx
where salary >
(select avg(salary)
from employee empy
where empy.dept = empx.dept);
The preceding subquery uses a correlation name (empx) defined in the outer query. The reference, empx.dept, must be explicitly qualified here. Otherwise the dept column is assumed to be implicitly qualified by empy. The overall query is evaluated by assigning empx each of its values from the employee table and evaluating the subquery for each value of empx.
Note: Although aggregate functions cannot appear directly in a where clause, they can appear in the select clause or the having clause of a subselect, which itself appears within a where clause.
|
|
|
Derived tables let you create or simplify complex queries. Useful in data warehousing applications, they provide a way to isolate complex portions of query syntax from the rest of a query.
A derived table is the coding of a SELECT in the FROM clause of a SELECT statement.
For example:
select relid, x.attname
from (select attrelid, attrelidx, attname,
attfrml from iiattribute) x, iirelation
where reltid = attrelid and reltidx = x.attrelidx
The derived table behaves like an inline view; the rows of the result set of the derived table are read and joined to the rest of the query. If possible, the derived table is flattened into the containing query to permit the query compiler to better optimize the query as a whole.
Some complex queries cannot be implemented without using either pre-defined views or derived tables. The derived table approach is more concise than pre-defined views, and avoids having to define persistent objects, such as views, that may be used for a single query only.
For example, consider a query that joins information to some aggregate expressions. Derived tables allow the aggregates to be defined and joined to non-aggregated rows of some other tables all in the same query. Without derived tables, a persistent view would have to be defined to compute the aggregates. Then a query would have to be coded to join the aggregate view to the non-aggregated data.
|
|
|
The SELECT in the FROM clause must be enclosed in parentheses and must include a "correlation name."
Following the correlation name, the derived table can include an override list of column names in parentheses, or these column names can be coded with AS clauses in the SELECT list of the derived table.
Columns in the derived table can be referenced in SELECT, ON, WHERE, GROUP BY, and HAVING clauses of the containing query, qualified by the correlation name, if necessary.
Example Queries Using Derived Tables
select e.ename from employee e,
(select avg(e1.salary), e1.dno from employee e1
group by e1.dno) e2 (avgsal, dno)
where e.dno = e2.dno and e.salary > e2.salary
Changing columns names with AS clause:
select e.ename from employee e,
(select avg(e1.salary) as avgsal, e1.dno from employee e1
group by e1.dno) e2
where e.dno = e2.dno and e.salary > e2.salary