Conceptual Description of the Selection Process
This section presents a conceptual step-by-step analysis of the evaluation of a
It is intended as an aid in formulating complex
SELECTstatements, 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 JOIN items AS i ON p.producer_id = i.producer_id GROUP BY p.producer HAVING COUNT(*) > 10 ORDER BY average DESC, producer OFFSET 2 FETCH FIRST 3 ROWS
- 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
- The FROM clause defines an intermediate result set.
Tables addressed in the
FROMclause 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
FROMclause in the example produces an intermediate result set which is the full cross product of the
PRODUCERStable and the
- The WHERE clause selects rows from the intermediate set.
WHEREclause 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
WHEREclause selects only those result set rows where the value in the
PRODUCER_IDcolumn from the
PRODUCERStable is equal to that in the
PRODUCER_IDcolumn from the
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, EXCEPT and INTERSECT are merged.
This example does not include a UNION, EXCEPT or INTERSECT.
- The current result is sorted according to the ORDER BY clause:ORDER BY average DESC, producer;
- The final result is chosen according to the OFFSET and FETCH FIRST clauses:OFFSET 2 FETCH FIRST 3 ROWS
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40