Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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.

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

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

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

  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 G.RESERVATION,
        G.GUEST_LNAME, 
        SUM(B.COST)
 
 

 G.RESERVATION 
 G.GUEST_LNAME
  
 1348 
 JOHANSEN    
 200
 1349 
 HANSEN    
 70
 ...
 ...
 ...
  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 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
Mimer SQL User's Manual TOC PREV NEXT INDEX