Progress
SQL-92
Guide and Reference


Query Specification

This is the syntax for a query_specification:

SYNTAX
SELECT [ ALL | DISTINCT ]
  { * 
    | { table_name | alias.} * [ , { table_name.| alias.} * ] ... 
    | expr [ [ AS ] [ ’ ] column_title [ ’ ] ] 
        [, expr [ [ AS ] [ ’ ] column_title [  ] ] ] ... 
  }
FROM table_ref [, table_ref ] ... [ { NO REORDER } ]
  [ WHERE search_condition ]
  [ GROUP BY [ table.]column_name [, [ table.]column_name ] ...
  [ HAVING search_condition ] ; 

SELECT [ ALL | DISTINCT ]

DISTINCT specifies that the result table omits duplicate rows. The default, ALL, specifies that the result table includes all rows.

SELECT * | { table_name. | alias. } *

Specifies that the result table includes all columns from all tables named in the FROM clause.

SELECT expr [ [ AS ] [ ’ ] column_title [ ’ ] ]

Specifies a list of expressions, called a select list, whose results will form columns of the result table. Typically, the expression is a column name from a table named in the FROM clause. The expression can also be any supported mathematical expression, scalar function, or aggregate function that returns a value.

The optional ‘column_title argument specifies a new heading for the associated column in the result table. You can also use the column_title in an ORDER BY clause.

FROM table_ref ...

Specifies one or more table references. Each table reference resolves to one table (either a table stored in the database or a virtual table resulting from processing the table reference) whose rows the query expression uses to create the result table. There are three forms of table references:

table_ref

SYNTAX
table_name [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]
  | ( query_expression ) [ AS ] alias [ (column_alias [ ... ] ) ] 
  | [ ( ] joined_table [ ) ] 

FROM table_name [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]

Explicitly names a table. The name can be a table name, a view name, or a synonym.

An alias is a name you use to qualify column names in other parts of the query expression. Aliases are also called correlation names.

If you specify an alias, you must use it, and not the table name, to qualify column names that refer to the table. Query expressions that join a table with itself must use aliases to distinguish between references to column names.

For example, the following query expression joins the table customer with itself. It uses the aliases x and y and returns information on customers in the same city as customer SMITH:

Similar to table aliases, the column_alias provides an alternative name to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in table_name. Also, if you specify column aliases in the FROM clause, you must use them, and not the column names, in references to the columns.

FROM ( query_expression ) [ AS ] [ alias [ ( column_alias [ ... ] ) ] ]

Specifies a derived table through a query expression. With derived tables, you must specify an alias to identify the derived table.

Derived tables can also specify column aliases. Column aliases provide alternative names to use in column references elsewhere in the query expression. If you specify column aliases, you must specify them for all the columns in the result table of the query expression. Also, if you specify column aliases in the FROM clause, you must use them, and not the column names, in references to the columns.

FROM [ ( ] joined_table [ ) ]

Combines data from two table references by specifying a join condition. The syntax currently allowed in the FROM clause supports only a CROSS JOIN, INNER JOIN, or LEFT OUTER JOIN.

CROSS JOIN specifies a Cartesian product of rows in the two tables. Every row in one table is joined to every row in the other table.

INNER JOIN specifies an inner join using the supplied search condition.

LEFT OUTER JOIN specifies a left outer join using the supplied search condition.

You can also specify these and other join conditions in the WHERE clause of a query expression. See the "Outer Joins" section for more information on both ways of specifying outer joins.

joined_table

SYNTAX
{ table_ref CROSS JOIN table_ref 
  | table_ref [ INNER | LEFT [ OUTER ] ] JOIN 
    table_ref ON search_condition 
} 

See the section on "Inner Joins" , Cross Joins, and "Outer Joins" for more details.

WHERE search_condition

Specifies a search_condition that applies conditions to restrict the number of rows in the result table. If the query expression does not specify a WHERE clause, the result table includes all the rows of the specified table reference in the FROM clause.

The search_condition is applied to each row of the result table set of the FROM clause. Only rows that satisfy the conditions become part of the result table. If the result of the search_condition is NULL for a row, the row is not selected. Search conditions can specify different conditions for joining two or more tables. See the "Outer Joins" section for more information.

The search_condition is applied to each row of the result table set of the FROM clause. Only rows that satisfy the conditions become part of the result table. If the result of the search_condition is NULL for a row, the row is not selected.

Search conditions can specify different conditions for joining two or more tables. See the "Outer Joins" and "Search Conditions" sections for information on the different kinds of search conditions.

GROUP BY [ table_name.]column_name ...

Specifies grouping of rows in the result table.

For the first column specified in the GROUP BY clause, SQL arranges rows of the result table into groups whose rows all have the same values for the specified column.

If you specify a second GROUP BY column, SQL groups rows in each main group by values of the second column.

SQL groups rows for values in additional GROUP BY columns in a similar fashion.

All columns named in the GROUP BY clause must also be in the select list of the query expression. Conversely, columns in the select list must also be in the GROUP BY clause or be part of an aggregate function.

HAVING search_condition

Allows you to set conditions on the groups returned by the GROUP BY clause. If the HAVING clause is used without the GROUP BY clause, the implicit group against which the search condition is evaluated is all the rows returned by the WHERE clause.

A condition of the HAVING clause can compare one aggregate function value with another aggregate function value or a constant.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095