6.1.5 Qualifying a name
You can qualify a name so that you can explicitly specify a schema name or make a name unique, among other uses. You qualify a name by connecting one name (such as a schema name) to another name (such as a table identifier) using a dot (.).
- Organization of this subsection
(1) Schema name specification format
- Format
-
schema-name ::= schema-identifier
Specify a schema name as a schema identifier.
If the owner of the table or index specified in the SQL statement is the HADB user who is connected to the HADB server, this user specifies his or her own authorization identifier.
If the schema name is omitted in the SQL statement, the authorization identifier specified when connecting to the HADB server is assumed as the schema name.
To search a dictionary table or system table, specify MASTER as the schema name.
(2) Table name specification format
- Format
-
table-name ::= [schema-name.]table-identifier
Specify a table name as a table identifier, optionally qualified by a schema name. Specify either a base table name or a viewed table name as the table identifier.
If the schema name is omitted in the SQL statement, the authorization identifier specified when connecting to the HADB server is assumed as the schema name.
(3) Index name specification format
- Format
-
index-name ::= [schema-name.]index-identifier
Specify an index name as an index identifier, optionally qualified by a schema name.
If the schema name is omitted in the SQL statement, the authorization identifier specified when connecting to the HADB server is assumed as the schema name.
(4) Table specification format
If you specify two or more tables in a single SQL statement, in order to identify which table the specified column or asterisk (*) corresponds to, you must qualify it using a table name, query name, or correlation name to uniquely identify the table.
For details about query names, see (a) WITH-clause in (2) Explanation of specification format in 7.1.1 Specification format and rules for query expressions.
A correlation name is an alias for a table. It is used in the following circumstances:
-
When you want to join a table to itself
-
When you want to reference the columns of a table from an outer query after specifying the same table in a subquery
By specifying a correlation name, a single table can be treated as if it were two different tables.
- Format
-
table-specification ::= {table-name | query-name | correlation-name}
The following is an example of qualification using a table specification.
- Example:
-
In order to reference columns with the same name (DNO) in multiple tables (EMP, DEPT), qualify them with their table names.
SELECT "ENO","ENAME","EMP"."DNO","DNAME" FROM "EMP","DEPT" WHERE "EMP"."DNO"="DEPT"."DNO"The underlined portions are examples of qualification using a table specification.
(5) Column specification format
A column name that is qualified with a table specification is called a column specification.
- Format
-
column-specification ::= [table-specification.]column-name
A column name cannot be qualified with a table specification if it is not within the scope of the specified table name or query name. For details about the scope of table names and query names, see 6.8 Scope variables.
The column name must exist in the position where it is specified in the table, or derived table, whose scope it falls under. For rules about the column names of derived tables, see 6.9 Derived column names.
Some column names can be qualified while others cannot, due to syntactic considerations. The description column-specification in a format specification indicates a column name that can be qualified. The description column-name indicates that the column name cannot be qualified.
In the following case, a column name must be qualified with a table specification.
-
In a retrieval in which multiple tables are specified in one FROM clause (by joining two or more tables) and the multiple tables contain identically named columns (without a qualification, it would not be clear which table was intended).