Usage Notes - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Rules and Restrictions for the FROM Clause

The rules and restrictions are:

  • If the FROM clause refers to a table or view name that is duplicated in other, non-default, databases referenced in the query, you must use the fully-qualified form of the name, such as one of the following:
       database_name.table_name
       user_name.table_name
  • If a column reference is made from an inner query to an outer query, then it must be fully qualified with the appropriate table name from the FROM clause of the outer query.
  • Any subquery must contain a FROM clause, and every column referenced in the SELECT statement must be in a table referenced either in the FROM clause for the subquery or some FROM clause in an outer query containing the subquery.
The FROM clause is … In the main query of these SQL statements …
not required
  • ABORT
  • DELETE
  • SELECT
  • UPDATE
optional
  • ABORT
  • SELECT
  • UPDATE
optional depending on the syntax used DELETE
  • During a self-join operation, related data selected from different rows of the same table is combined and returned as a single row. The temporary table names that are defined using the FROM clause qualify different references to the same table columns.

    The following table lists the effects of column references on self-join processing. Note that these rules apply only to cases where a single aliasing FROM clause is supplied.

IF this column reference … IS … THEN a self-join is …
all unqualified (the preceding table name or correlation name is omitted) not performed.
qualified (but the qualifiers reference only the correlation name)
some, but not all qualified and the qualifiers reference only the correlation name
qualified and the qualifiers reference only the permanent table name moot because no assumptions can be made about the owner of the unqualified columns. The self-join not performed.
  • When a FROM clause references both a permanent table name and a correlation name, a self-join can be performed depending on how the column references are qualified.

    For a successful self-join operation, column references must be fully qualified (that is, specified in the form tablename.columname) and the qualifiers should involve both the permanent and the correlation table names, as shown in the Example: FROM Clause Used for a Self-Join

    Once a correlation name is declared, any subsequent reference to the base table name causes a new instance of the table to be used; the result of the SELECT can be a Cartesian self-join.

    In Example: FROM Clause Used for a Self-Join table_1 is given the correlation name t. The subsequent specification of table_1 in the WHERE clause then causes the statement to perform a Cartesian product.

         SELECT *
         FROM table_1 AS t
         WHERE table_1.column_1 = 2;