Progress
SQL-92
Guide and Reference
Query Specification
This is the syntax for a query_specification:
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 thecolumn_title
in anORDER 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:
- A direct reference to a table, view, or synonym.
- A derived table specified by a query expression in the
FROM
clause.- A joined table that combines rows and columns from multiple tables.
The usage notes specific to each form of table reference are below.
If there are multiple table references, SQL joins the tables to form an intermediate result table that is used as the basis for evaluating all other clauses in the query expression. That intermediate result table is the Cartesian product of rows in the tables in the
FROM
clause, formed by concatenating every row of every table with all other rows in all tables.table_ref
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 intable_name
. Also, if you specify column aliases in theFROM
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
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 aWHERE
clause, the result table includes all the rows of the specified table reference in theFROM
clause.The
search_condition
is applied to each row of the result table set of theFROM
clause. Only rows that satisfy the conditions become part of the result table. If the result of thesearch_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 theFROM
clause. Only rows that satisfy the conditions become part of the result table. If the result of thesearch_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 theGROUP 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 theHAVING
clause is used without theGROUP BY
clause, the implicit group against which the search condition is evaluated is all the rows returned by theWHERE
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 |