www.mimer.com/developer

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 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;

Result:

RESERVATION	GUEST_LNAME
1351	ALBERTSON	420
1359	ALVE	100
1356	ANDERSSON	200
1401	BLOM	500
1358	CODD	100
1353	FIMPLEY	790
1352	FRANCIS	-
1397	GRANKVIST	100
1349	HANSEN	70
1404	HANSSON	500
1413	HEDIN	300
1391	HESTMAN	420
1361	HOLLINGSWORTH	100
1364	HOLLSTEN	200
1379	HOLMER	300
1348	JOHANSEN	200
1367	JOHNSSON	-
1374	KARLSSON	600
1372	KRISTOFERSEN	-
1388	KULLMER	440
1396	LAHTINEN	340
1363	LE FEVRE	740
1393	LE FEVRE	400
1383	LIND	240
1381	LINDE	900
1386	LUNDBECK	395
1357	NILSSON	455
1385	NYQVIST	600
1369	OLSSON	140
1370	OLSSON	100
1382	PEREZ	1310
1384	PERSSON	720
1392	PERSSON	1350
1398	RYDELL	100
1368	SCHLAGER	-
1395	SCHMIDT	200
1405	SELLIN	320
1389	SMITH	100
...	...	...

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

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

4. The GROUP BY clause groups the remaining result set

 GROUP BY G.RESERVATION, G.GUEST_LNAME
 

G.RESERVATION	G.GUEST_LNAME	B.RESERVATION	B.COST
1347	DATE	1347	100
1347	DATE	1347	40
1347	DATE	1347	40
1348	JOHANSEN	1348	120
1348	JOHANSEN	1348	40
1348	JOHANSEN	1348	40
1349	HANSEN	1349	70
...	...	...	...

5. The HAVING clause selects groups

 
 HAVING G.RESERVATION > 1347
 

G.RESERVATION	G.GUEST	B.RESERVATION	B.COST
1348	JOHANSEN	1348	120
1348	JOHANSEN	1348	40
1348	JOHANSEN	1348	40
1349	HANSEN	1349	70
...	...	...	...

6. 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)
 
 


  

  

    
 G.RESERVATION 


    
 G.GUEST_LNAME


    
  


  
  

    
 1348 


    
 JOHANSEN    


    
 200


  
  

    
 1349 


    
 HANSEN    


    
 70


  
  

    
 ...


    
 ...


    
 ...


  

7. The results of subselects joined by UNION are merged

   This example does not include a UNION.

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

 
 ORDER BY GUEST_LNAME;
 

RESERVATION	GUEST_LNAME
1349	HANSEN	70
1348	JOHANSEN	200
...	...	...

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se