next up previous
Next: UPDATES IN SQL Up: QUERYING IN SQL Previous: Full Select Statement Syntax

Conceptual Order of Evaluation of a Select Statement

  1. First the product of all tables in the from clause is formed.
  2. The where clause is then evaluated to eliminate rows that do not satisfy the search_condition.
  3. Next, the rows are grouped using the columns in the group by clause.
  4. Then, Groups that do not satisfy the search_condition in the having clause are eliminated.
  5. Next, the expressions in the select clause target list are evaluated.
  6. If the distinct keyword in present in the select clause, duplicate rows are now eliminated.
  7. The union is taken after each sub-select is evaluated.
  8. Finally, the resulting rows are sorted according to the columns specified in the order by clause.

(1) Get names and ids of customers and agents along with
    total dollar sales for that pair. Order the result
    from largest to smallest total sales. Also retain
    only those pairs for which total dollar sales is
    at least 900.00.

SQL> select c.cname, c.cid, a.aname, a.aid, sum(o.dollars)
     from customers c, orders o, agents a
     where c.cid = o.cid and o.aid = a.aid
     group by  c.cname, c.cid, a.aname, a.aid
     having sum(o.dollars)  >= 900.00
     order by 5 desc;

------------- ---- ------------- --- --------------
Allied        c003 Brown         a03           2208
Tiptop        c001 Otasi         a05           1440
Tiptop        c001 Smith         a01            900

Raj Sunderraman
Tue Apr 1 16:15:10 PST 1997