Usage Notes - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
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 1: 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 1: FROM Clause Used for a Self‑Join,” e, 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;