Column Specification

A column specification is used to identify a column (which must contain a column identifier) in an SQL statement.

Column specification is used:

to define a table column in a CREATE TABLE, CREATE TABLE DESCRIPTION or CREATE VIEW statement.

to represent the column value in a SELECT clause expression or in a search.

in WHERE, HAVING, GROUP BY or ORDER BY clauses, to represent all the result values after the clause is applied.

to represent all the row values resulting from the grouping operation as a function argument.

A column specification can be either a qualified column specification or an unqualified column identifier.

A qualified column specification explicitly specifies the related table.

An unqualified column identifier does not explicitly specify the related table.

Here are the rules used to relate a column specification to one and only one table specification:

1. The current query specification occurs in the column specification.

2. Successive query specifications are analyzed starting with the current query.

3. The candidate table specification is the first table specification containing the column specification definition.

If the candidate table is contained in a higher query specification than the current one, the column is an outer reference.

An unqualified column specification can be used when a column can relate unambiguously to one table (only one table within the same query specification contains the column identifier).

A qualified column specification consists of a table specification followed by a column identifier separated by a period. Use a qualified column specification when a column cannot be unambiguously related to a table (multiple tables in the same query specification contain columns with the same column identifier).

This example shows how the column specification distinguishes between two columns of the same name in different tables. Specify both tables in the FROM clause.

Use a qualified column specification when the same table may need to be referenced more than once in the same query specification. You cannot qualify the column identifier with the table specification. Instead, use a correlation identifier to distinguish between the different references of the same table (see Table Specification for more information).

For example, use the following syntax to find the least expensive cruise for each destination. Correlate the first instance of the table cruise with the letter X because the sub-query needs to distinguish between two identical column references on two different 'instances' of the same table.

SELECT cruise_id,start_harbor,cruise_price
FROM cruise X
WHERE cruise_price = ( SELECT MIN(cruise_price)
FROM cruise
WHERE destination_harbor = X.destination_harbor );

Outer references are a special type of qualified column specification.

An outer reference is a reference to a table column specified in a higher-level query specification. They are required if a column identifier cannot be unambiguously related to a single table. Using qualified column specifications for outer references increases SQL statement readability.

The following example identifies all contracts that cost more than double the cruise price of the cruises that the contracts identify. The id_cruise column is an outer reference as the table it references is contained in the higher query specification.

SELECT contract_id FROM contract
WHERE (price*2) > (SELECT cruise_price FROM cruise
WHERE cruise_id = contract.id_cruise );

Naming result table columns is a part of the SQL-2 standard entry level. This function can be used to change the displayed column name of a result table. For example: