Conceptual Description of the Selection Process
This section presents a conceptual step-by-step analysis of the evaluation of a SELECT statement.
It is intended as an aid in formulating complex SELECT statements, and can also help you in understanding details of the statement syntax.
Note: The description here is purely conceptual. It does not represent the actual sequence of events performed by the database manager. In particular, the computer resource requirements implied by the intermediate result set defined in a FROM clause do not necessarily reflect actual requirements.
The query used in the analysis is:
List those producers and the average price for the goods that they manufacture where they make more than 10 items. Sort the result by the average price, with the largest first:SELECT producer, AVG(price) AS average FROM producers AS p, items AS i WHERE p.producer_id = i.producer_id GROUP BY p.producer HAVING COUNT(*) > 10 ORDER BY average DESC, producer;
- Subselects at the lowest nesting level are evaluated first.
The first step in evaluating a select is to resolve subselects from the lowest level up, and conceptually replace the subselect with the result set. The example here does not use a nested select.
When all subselects are resolved, a, possibly complicated, single-level SELECT statement remains.
- The FROM clause defines an intermediate result set.
Tables addressed in the FROM clause are combined to form an intermediate result set which is the full cross product of the tables.
The cross product is a table with one column for each column in each of the table, and one row for every combination of rows from the different tables.
The columns in the result set are identified by the qualified column names from the table from which they are derived.FROM producers AS p, items AS i
The FROM clause in the example produces an intermediate result set which is the full cross product of the PRODUCERS table and the ITEMS table.
- The WHERE clause selects rows from the intermediate set.
The WHERE clause selects rows from the full cross product result set that meet the criteria specified.WHERE p.producer_id = i.producer_id
In this example the WHERE clause selects only those result set rows where the value in the PRODUCER_ID column from the PRODUCERS table is equal to that in the PRODUCER_ID column from the ITEMS table.
The GROUP BY clause groups the remaining result set:GROUP BY p.producer
- The HAVING clause selects groups:HAVING COUNT(*) > 10
- The SELECT list selects columns, evaluates any expressions in the SELECT list, and reduces groups to single rows if set functions are used:SELECT producer, AVG(price) AS average
- The results of subselects joined by UNION are merged.
This example does not include a UNION.
- The final result is sorted according to the ORDER BY clause:ORDER BY average DESC, producer;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40