Database Reference
In-Depth Information
The following example, based on the
subquery_coalescing.sql
script, illustrates how this query transformation
works. Notice that the two correlated subqueries process the same data. Only the restrictions differ:
SELECT *
FROM t1
WHERE EXISTS (SELECT 1
FROM t2
WHERE t2.id = t1.id AND t2.n > 10)
OR EXISTS (SELECT 1
FROM t2
WHERE t2.id = t1.id AND t2.n < 100)
Subquery coalescing combines the two subqueries and produces the following query:
SELECT *
FROM t1
WHERE EXISTS (SELECT 1
FROM t2
WHERE t2.id = t1.id AND (t2.n > 10 OR t2.n < 100))
With subquery unnesting, the query can be transformed further as follows. Notice that, as explained in the
“Subquery Unnesting” section, this next query uses a special operator (
s=
) to implement the semi-join. That operator,
available internally to the SQL engine, isn't part of the syntax that you can specify when writing a SQL statement:
SELECT t1.*
FROM t1, t2
WHERE t1.id s= t2.id AND (t2.n > 10 OR t2.n < 100)
Subquery Removal Using Window Functions
The purpose of
subquery removal using window functions
is to replace subqueries containing aggregate functions
with window functions. This heuristic-based query transformation can be applied when a query block contains all the
tables and predicates appearing in a subquery.
The following example, based on the
subquery_removal.sql
script, illustrates how this query transformation
works. Notice that the table
t2
is referenced in the top-level query block as well as in the subquery:
SELECT t1.id, t1.n, t2.id, t2.n
FROM t1, t2
WHERE t1.id = t2.t1_id
AND t2.n = (SELECT max(n)
FROM t2
WHERE t2.t1_id = t1.id)
The query transformation removes the subquery and produces the following query. Notice how the
CASE
expression is used to generate a kind of flag by which to recognize the rows fulfilling the restriction that, in the original
query, is specified by the subquery:
SELECT t1_id, t1_n, t2_id, t2_n
FROM (SELECT t1.id AS t1_id, t1.n AS t1_n, t2.id AS t2_id, t2.n AS t2_n,
CASE t2.n
