You name columns in CREATE TABLE and ALTER TABLE statements. In
other SQL statements, the names you give to columns in CREATE and
ALTER statements can be qualified by table names, view names, or
correlation names.
NOTE
In syntax diagrams, the column-name syntax element refers to
either the qualified or unqualified form of the name given
to the column in the CREATE TABLE or ALTER TABLE statement.
That is, in syntax diagrams, column-name is always defined
as:
(B)0[m[1mcolumn-name = [m
[1m [m
[1mqqqwqqqqqqqqqqq>qqqqqqqqqqqqqqqqqwq> <name-of-column> qq> [m
[1m tq> <table-name> qqqqqqqw> . qj [m
[1m tq> <view-name> qqqqqqqqu [m
[1m mq> <correlation-name> qj [m
[1m [m
The only time you must qualify column names is when they are
ambiguous. Joining a table with itself and joining two tables
with common column names are two cases that require qualified
column names. Also, if you have a parameter without a colon with
the same name as a column, you need to qualify references to that
column.
1 – Correlation Names
In addition to qualifying column names with table names or view
names, you can qualify column names with correlation names.
Correlation names are analogous to aliases, but they refer to
tables instead of databases. Just as aliases provide temporary
names for databases to qualify ambiguous table names, correlation
names give temporary names to tables to qualify ambiguous column
names.
Specify a correlation name after a table name within the FROM
clause of a select expression or DELETE statement, or in an
UPDATE statement. Use any valid name that has not already been
used in the FROM clause either as a correlation name or as a
table name without a correlation name.
You must use correlation names to qualify column names in
statements that join a table with itself. As with table names
and view names, however, you can always specify a correlation
name for clarity
2 – Outer References
You may have to qualify column names in an outer reference.
An outer reference is a reference within a subquery to a table
specified in an outer query that contains the subquery. An outer
reference is also called a correlated reference.
For example, the previous example that retrieved the names
of employees who worked in the marketing department can be
reformulated to use an outer reference.
SQL> SELECT FIRST_NAME, --
cont> LAST_NAME --
cont> FROM EMPLOYEES --
cont> WHERE 'MKTG' IN --
cont> (SELECT DEPARTMENT_CODE -- -- Outer
cont> FROM JOB_HISTORY -- -- Query
cont> WHERE JOB_END IS NULL -- Sub- --
cont> AND -- query --
cont> EMPLOYEE_ID =
cont> EMPLOYEES.EMPLOYEE_ID) -- --
cont> -- ---------------------
cont> -- outer reference
cont> ;
FIRST_NAME LAST_NAME
Chris Danzig
Lawrence Hall
Paul Belliveau
3 rows selected
If the outer reference to EMPLOYEE_ID in this example were not
qualified by the table name EMPLOYEES, it would refer to the
EMPLOYEE_ID column in the subquery, not the outer query. The
predicate EMPLOYEE_ID = EMPLOYEE_ID is true for all values
of EMPLOYEE_ID that are not null, so the statement would not
generate an error, but would give unexpected results. Instead of
the three marketing employees, it would select all rows of the
EMPLOYEES table with values in the EMPLOYEE_ID column that were
not null.
Although the outer reference is contained within a subquery,
it receives its value from an outer query. Because of this,
the subquery must be evaluated once for each value that the
outer reference receives from the outer query. It is this
characteristic that defines an outer reference.