Declarative Query Processing in Relational Database Systems - Automated Physical Database Design and Tuning - page 9

3. Evaluate the predicate in the WHERE clause over each row in the Cartesian
product, eliminating rows that do not satisfy the predicate.
4. Group the remaining tuples, where each group has the same values for
all columns in the GROUP BY clause.
5. Obtain a row for each group, which consists of evaluating the expressions
in the SELECT clause (including aggregates).
6. Evaluate the predicate in the HAVING clause on each representative row,
eliminating those that do not satisfy the predicate.
7. Sort the tuples by the columns in the ORDER BY clause.
To illustrate the expressive power of the SELECT SQL statement, consider
again tables Emp and Dept in Figure 1.2, and suppose that we want to obtain
a list of all departments in Seattle, sorted by their budget. We can then use
the following query:
SELECT Dept.DId
FROM Dept
WHERE Dept.City = 'Seattle'
ORDER BY Dept.Budget
Suppose now that we want to obtain the names of all employees in depart-
ments with over $10 million in budget. We can then issue any of the following
three SQL queries:
Q1 = SELECT Name
FROM Emp, Dept
WHERE Emp.DId = Dept.DId AND Dept.Budget > 10M
Q2 = SELECT Name
FROM Emp, ( SELECT Dept.DId FROM Dept
WHERE Dept.Budget > 10M ) as LargeD
WHERE Emp.DId = LargeD.DId
Q3 = SELECT Name
FROM Emp
WHERE Emp.DId IN (SELECT Dept.DId FROM Dept
WHERE Dept.Budget > 10M )
Note that while Q1 is expressed using a single block, Q2 uses a nested sub-
query in the FROM clause. In turn, Q3 uses a nested subquery in the WHERE
clause, leveraging the relational operator IN , which returns true if the left
operand is present in a single-column table specified by the second relational
operand. Note that all these queries are equivalent.