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 the total amount due for reservations above number 1347. Sort the result by guest name:SELECT G.RESERVATION, G.GUEST_LNAME, SUM(B.COST) FROM BOOK_GUEST G, BILL B WHERE G.RESERVATION = B.RESERVATION GROUP BY G.RESERVATION, G.GUEST_LNAME HAVING G.RESERVATION > 1347 ORDER BY GUEST_LNAME;
- 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 BOOK_GUEST G, BILL B
The FROM clause in the example produces an intermediate result set which is the full cross product of the BOOK_GUEST table and the BILL 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 G.RESERVATION = B.RESERVATION
In this example the WHERE clause selects only those result set rows where the value in the RESERVATION column from the BOOK_GUEST table is equal to that in the RESERVATION column from the BILL table.
- The GROUP BY clause groups the remaining result setGROUP BY G.RESERVATION, G.GUEST_LNAME
- The HAVING clause selects groupsHAVING G.RESERVATION > 1347
- The SELECT list selects columns, evaluates any expressions in the SELECT list, and reduces groups to single rows if set functions are used.SELECT G.RESERVATION, G.GUEST_LNAME, SUM(B.COST)
- 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 clauseORDER BY GUEST_LNAME;
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40