Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


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.

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

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

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

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

    This example does not include a UNION.

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



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX