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.

Query Used

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;

Result:

PRODUCER	AVERAGE
BBC Audio (Spoken Word)	37.742727272727
MCA	27.798181818181
RCA	19.580000000000
Elektra/Asylum	18.265714285714
Warner Brothers	17.137894736842
Capitol	16.646666666666
Atlantic	14.798181818181
Sony	14.091111111111
...	...

Selection Process

1. 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.

2. 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.

3. 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
 

PRODUCER	PRICE
404 Music Group	16.98
4AD Records	11.98
7-N Music	16.98
A&M Records	11.98
A&M Records	22.98
A&M Records	10.98
A&M Records	18.98
A&M Records	18.98
...	...

4. The HAVING clause selects groups:

 HAVING COUNT(*) > 10
 

PRODUCER	PRICE
Atlantic	17.98
Atlantic	11.98
Atlantic	11.98
Atlantic	9.98
Atlantic	11.98
Atlantic	17.98
Atlantic	11.98
Atlantic	11.98
...	...

5. 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

PRODUCER	AVERAGE
Atlantic	14.798181818181
BBC Audio (Spoken Word)	37.742727272727
Capitol	16.646666666666
Collins	7.529814814814
Elektra/Asylum	18.265714285714
Geffen Records	12.480000000000
HarperCollins	6.722187500000
Marshall Editions	9.842222222222
...	...

6. The results of subselects joined by UNION are merged.

   This example does not include a UNION.

7. The final result is sorted according to the ORDER BY clause:

 
 ORDER BY average DESC, producer;

PRODUCER	AVERAGE
BBC Audio (Spoken Word)	37.742727272727
MCA	27.798181818181
RCA	19.580000000000
Elektra/Asylum	18.265714285714
Warner Brothers	17.137894736842
Capitol	16.646666666666
Atlantic	14.798181818181
Sony	14.091111111111
...	...

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se