Query Evaluation

This section describes the logic applied to the evaluation of select statements. While the logic presented here does not precisely reflect how the DBMS Server evaluates your query to determine the most efficient way to return results, by applying this logic to your queries and data, the results of your queries can be anticipated.

1. Evaluate the from clause. Combine all the sources specified in the from clause to create a Cartesian product (a table composed of all the rows and columns of the sources). If joins are specified, evaluate each join to obtain its results table, combine it with the other sources in the from clause. If select distinct is specified, discard duplicate rows.
2. Apply the where clause. Discard rows in the result table that do not fulfill the restrictions specified in the where clause.
3. Apply the group by clause. Group results according to the columns specified in the group by clause.
4. Apply the having clause. Discard rows in the result table that do not fulfill the restrictions specified in the having clause.
5. Evaluate the select clause. Discard columns that are not specified in the select clause.
6. Perform any unions. Combine result tables as specified in the union clause.
7. Apply the order by clause. Sort the result rows as specified.

Specifying Tables and Views

The following section describes how to specify table names in queries. The same rules apply to views.

To select data from a table you own, specify the name. To select data from a table you do not own, specify schema.table, where schema is the name of the user that owns the table. However, if the table is owned by the database DBA, the schema qualifier is not required. You must have the appropriate permissions to access the table (or view) granted by the owner.

A correlation name can be specified for any table in the from clause. A correlation name is an alias (or alternate name) for the table. For example:

select... from employees e, managers m...

The preceding example assigns the correlation name 'e' to the employees table and 'm' to the managers table. Correlation names are useful for abbreviating long table names and for queries that join columns in the same table.

If a correlation name is assigned to a table, the table must be referred to by the correlation name. For example:

Correct:

select e.name, m.name
from employees e, managers m...

Incorrect:

select employees.name, managers.name
from employees e, managers m...