FROM Clause

Purpose

Defines either of these items:

The set of base tables, global temporary tables, volatile tables, derived tables, views, or table functions that are referenced by the SELECT statement or query.

A correlation name for a base table, global temporary table, volatile table, derived table, or view for a self-join operation. See “Self‑Join” on page 252.

For information about syntax that is compatible with temporal tables, see:

ANSI Temporal Table Support

Temporal Table Support

Syntax

where:

Syntax Element …	Specifies …
Single Tables A FROM clause can include a sequence of single table references. This action creates an implicit inner join, which is to be distinguished from explicit joins where the keyword JOIN is part of the syntax.
table_name	the name of a base table, global temporary table, volatile table, derived table or view. If the database is omitted, the system infers it from context.
AS	an optional introduction to correlation_name. The use of AS introducing correlation_name is optional.
correlation_name	an optional alias for the table that is referenced by table_name. Note that you must specify a correlation_name for a self-join. ANSI SQL refers to table aliases as correlation names. They are also referred to as range variables.
Joined Tables Options for joined tables allows the FROM clause to specify multiple tables joined in explicit ways, as described below.
joined_table	either a single table name with optional alias name, or a joined table, indicating nested joins.
INNER	a join in which qualifying rows from one table are combined with qualifying rows from another table according to some join condition. This is the default join type.
OUTER	a join in which qualifying rows from one table that do not have matches in the other table, are included in the join result along with the matching rows from the inner join. The rows from the first table are extended with nulls.
LEFT OUTER JOIN	a left outer join. LEFT indicates the table that was listed first in the FROM clause. In a LEFT OUTER JOIN, matching rows as well as the rows from the left table that are not returned in the result of the inner join of the two tables, are returned in the outer join result and extended with nulls.
RIGHT OUTER JOIN	a right outer join. RIGHT indicates the table that was listed second in the FROM clause. In a RIGHT OUTER JOIN, matching rows as well as the rows from the right table that are not returned in the result of the inner join of the two tables, are returned in the outer join result and extended with nulls.
FULL OUTER JOIN	a full outer join. FULL OUTER JOIN returns rows from both tables. In a FULL OUTER JOIN, matching rows as well as rows from both tables that have not been returned in the result of the inner join, are returned in the outer join result and extended with nulls.
CROSS JOIN	a cross join. A CROSS JOIN is an unconstrained, or extended, Cartesian join. Cross joins return the concatenation of all rows from the tables specified in its arguments. Two joined tables can be cross joined.
single_table	the name of a single base or derived table or view on a single table to be cross joined with joined_table.
ON search_condition	one or more conditional expressions that must be satisfied by the result rows. An ON condition clause is required for each INNER JOIN or OUTER JOIN specified in an outer join expression.
Derived Tables The derived table allows the FROM clause to specify selected data from an underlying table set. The derived table acts like a viewed table.
subquery	the subquery that defines the derived table contents. The subquery cannot specify SELECT AND CONSUME.
AS derived_table_name	an assigned name for the temporary derived table. AS is an optional introductory keyword for derived table.
column_name	a list of column names or expressions listed in the subquery. Enables referencing subquery columns by name.
Table Function‑Derived Tables Use the TABLE option with the FROM clause to invoke a table user-defined function that returns row results as a derived table. The TABLE option can only be specified once in a FROM clause. The TABLE option cannot be specified in a FROM clause that also specifies the JOIN option.
TABLE function_name	the name of the table user-defined function.
expression	any valid SQL expression.
RETURNS database_name.table_ name	the name of the table and its containing database, if different from the current database, into which the rows processed by the dynamic result row table function are to be inserted.
RETURNS column_name- data_type	the name and data type of one or more columns to be assigned to the row fields returned by a dynamic result row table function. You cannot use the BLOB AS LOCATOR or CLOB AS LOCATOR forms to specify columns that have a BLOB or CLOB data type. See “CREATE FUNCTION (Table Form)” in SQL Data Definition Language for details.
HASH BY and LOCAL ORDER BY Clauses You use the HASH BY and LOCAL ORDER BY clauses to enforce the ordering of input to table UDFs.
HASH BY column_name	an optional set of column names on which to hash order globally the columns input to a table UDF. See “HASH BY Clause” on page 109.
LOCAL ORDER BY column_name LOCAL ORDER BY column_name ASC LOCAL ORDER BY column_name ASC NULLS FIRST LOCAL ORDER BY column_name ASC NULLS LAST LOCAL ORDER BY column_name DESC LOCAL ORDER BY column_name DESC NULLS FIRST LOCAL ORDER BY column_name DESC NULLS LAST	an optional set of column names on which to hash order on each AMP the columns input to a table UDF. See “LOCAL ORDER BY Clause” on page 113. ASC sorts the column in ascending order. This is the default. DESC sorts the column in descending order. NULLS FIRST sorts nulls at the beginning. NULLS LAST sorts nulls at the end.
AS derived_table_name	the name of a temporary derived table that other clauses in the SELECT statement can reference. AS is an optional introductory keyword for derived table.
AS derived_table_name column_name	an optional list of column names that other clauses in the SELECT statement can reference. If specified, the number of names in the list must match the number of columns in the RETURNS TABLE clause of the CREATE FUNCTION statement that installed the function_name table function on the Teradata platform. The alternate names list corresponds positionally to the corresponding column names in the RETURNS TABLE clause. If omitted, the names are the same as the column names in the RETURNS TABLE clause of the CREATE FUNCTION request that installed the function_name table function on the Teradata platform.
Table Operator Table operators read an input table, perform operations on the table such as partitioning or aggregation, then write output rows. Table operators can accept an arbitrary row format and based on the operation and input row type, they can produce an arbitrary output row format. For more information on the table operators that Teradata provides, see SQL Functions, Operators, Expressions, and Predicates. For more information on creating table operators, see SQL External Routine Programming.
operator_name	a name for the table operator. This name cannot be a Teradata reserved word.
ON table_name	the table expression that is input to the table operator.
ON (query_expression)	the query expression that is input to the table operator. You can have up to 16 ON clauses. You can use multiple ON clauses with all supported languages for protected and non-protected mode table operators. If you use multiple ON clauses, the order of the ON clauses must match the order of the stream numbers in the table operator that you use. You cannot use scalar subqueries in table operators with multiple ON clauses or ON clauses using PARTITION BY or HASH BY. Cogroups are used for table operators with multiple ON clauses. For information about cogroups, see SQL Functions, Operators, Expressions, and Predicates.
Hash or Partition By Clause
HASH BY column_name HASH BY column_position HASH BY column_expression	an optional set of column names on which to hash order globally the columns input to a table function or table operator. When you use a multiple input table operator that has multiple hash by clauses, the following restrictions apply: All of them must have the same number of partitioning attributes. The corresponding attributes must be the same type or matched using an implicit cast If you specify a LOCAL ORDER BY clause along with a HASH BY clause, the following restrictions apply: All the clauses must have the same number of LOCAL ORDER BY columns. The data types of the columns must be the same type or matched using an implicit cast. See “HASH BY Clause” on page 109.
PARTITION BY column_name PARTITION BY column_position PARTITION BY column_expression	to partition for a table specified as input to a table operator. You cannot specify a row-level security constraint column as a partitioning column. You can specify a column by name or position, or use an expression that resolves to a column. If you have multiple PARTITION BY clauses, the following restrictions apply: All of them must have the same number of partitioning attributes. The corresponding attributes must be the same type or matched using an implicit cast. If you specify an ORDER BY clause along with a PARTITION BY clause, the following restrictions apply: All the clauses must have the same number of ORDER BY columns. The data types of the columns must be the same type or matched using an implicit cast. Note: Partition keys are important to performance. If the partition keys hash to the same AMP, then some AMPs may be overloaded compared with others. Performance may also degrade if the partition key is a VARCHAR, as VARCHARs may take longer to compare than some other data types.
LOCAL ORDER BY column_name LOCAL ORDER BY column_position LOCAL ORDER BY column_expression LOCAL ORDER BY column_name ASC LOCAL ORDER BY column_name ASC NULLS FIRST LOCAL ORDER BY column_name ASC NULLS LAST LOCAL ORDER BY column_name DESC LOCAL ORDER BY column_name DESC NULLS FIRST LOCAL ORDER BY column_name DESC NULLS LAST	that qualified rows are ordered on each AMP in preparation to be input to a table function. See “LOCAL ORDER BY Clause” on page 113. You cannot use a LOCAL ORDER BY clause in addition to a DIMENSION clause in the same ON clause. If you use multiple ON clauses, you cannot specify a LOCAL ORDER BY clause in addition to a PARTITION BY ANY clause in the same ON clause. If you use multiple ON clauses and you specify only LOCAL ORDER BY in one of them, then all other ON clauses can only specify a DIMENSION clause. If you specify multiple HASH BY clauses with LOCAL ORDER BY clauses, the following restrictions apply: All the clauses must have the same number of LOCAL ORDER BY columns. The data types of the columns must be the same type or matched using an implicit cast. ASC sorts rows in ascending order. DESC sorts rows in descending order. NULLS FIRST sorts nulls at the beginning. NULLS LAST sorts nulls at the end.
ORDER BY column_name ORDER BY column_position ORDER BY column_expression ORDER BY column_name ASC ORDER BY column_name ASC NULLS FIRST ORDER BY column_name ASC NULLS LAST ORDER BY column_name DESC ORDER BY column_name DESC NULLS FIRST ORDER BY column_name DESC NULLS LAST	how result sets are sorted. If you do not use this clause, result rows are returned unsorted. You cannot specify ORDER BY as the only option in an ON clause. You must combine it with a PARTITION BY, PARTITION BY ANY, HASH BY, or DIMENSION clause. If you specify an ORDER BY clause along with a PARTITION BY clause, the following restrictions apply: All the clauses must have the same number of ORDER BY columns. The data types of the columns must be the same type or matched using an implicit cast. See “ORDER BY Clause” on page 221. ASC sorts rows in ascending order. DESC sorts rows in descending order. NULLS FIRST sorts nulls at the beginning. NULLS LAST sorts nulls at the end.
USING name (value)	one or more name-value pairs.
AS	optional keyword introducing correlation_name. ANSI SQL refers to aliases as correlation names. They are also referred to as range variables. If you have multiple ON clauses, each correlation name used in the query must be unique.
AS correlation_name	an alias for the column that is referenced by column_name.
AS correlation_name (column_name)	one or more column names.

ANSI Compliance

The FROM clause is ANSI SQL:2011-compliant with extensions.

A FROM clause is mandatory for SELECT statements in the ANSI SQL:2011 standard. The optional FROM clause in Teradata SQL is a Teradata extension.

The ANSI SQL:2011 standard also requires that all of the columns referenced in an outer query be in tables that are specified in the FROM clause, but Teradata SQL does not.

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.
all	qualified (but the qualifiers reference only the correlation name)
some, but not all	qualified and the qualifiers reference only the correlation name
some, but not all	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;

Example : FROM Clause Used for a Self‑Join

The following statement returns employees who have more years of work experience than their department managers:

     SELECT workers.name, workers.yrs_exp, workers.dept_no,

            managers.name, managers.yrs_exp

     FROM employee AS workers, employee AS managers

     WHERE managers.dept_no = workers.dept_no

     AND   UPPER (managers.jobtitle) IN ('MANAGER' OR 'VICE PRES')

     AND   workers.yrs_exp > managers.yrs_exp;

The FROM clause in the preceding statement enables the employee table to be processed as though it were two identical tables: one named workers and the other named managers.

As in a normal join operation, the WHERE clause defines the conditions of the join, establishing dept_no as the column whose values are common to both tables.

The statement is processed by first selecting managers rows that contain a value of either ‘MANAGER’ or ‘VICE PRES’ in the jobtitle column. These rows are then joined to the workers rows using a merge join operation with this join condition:

A workers row must contain a dept_no value that matches the dept_no value in a managers row.

The matching workers row must also contain a yrsexp value that is greater than the yrs_exp value in the managers row.

The following result is returned:

`name` `----`	`yrsexp` `------`	`dept_no` `-------`	`name` `----`	`yrsexp` `------`
Greene W	15	100	Jones M	13
Carter J	20	200	Watson L	8
Aguilar J	11	600	Regan R	10
Leidner P	13	300	Phan A	12
Ressel S	25	300	Phan A	12

Example : FROM Clause Left Outer Join

The following example illustrates a left outer join (see “Outer Joins” on page 253). In the example, the skills table lists various skills and the associated codes, and the emp table lists employee numbers and a skills codes.

skills		emp
skill_no	skill_name	emp_no	skill_no
1	baker	6123	1
2	doctor	6234	1
3	farmer	6392	3
4	lawyer	7281	5
5	mason	7362	4
6	tailor	6169	1

You can use this query to determine which skill areas do not have assigned employees:

     SELECT skills.skill_name, emp.emp_no

     FROM skills LEFT OUTER JOIN emp ON skills.skill_no=emp.skill_no;

The following result is returned. Notice that nulls are displayed as a QUESTION MARK (?) character, which is how BTEQ reports nulls (see Basic Teradata Query Reference for more information about BTEQ).

`skill_name` `----------`	`emp_no` `------`
baker	6123
baker	6234
baker	6169
doctor	?
farmer	6392
lawyer	7362
mason	7281
tailor	?

In order to include all skills in the result, you must specify an OUTER JOIN. An implicit join like this example that uses just the simple FROM clause does not return rows for nulls (that is, when there are no corresponding employees) and would not list doctor or tailor in the above result.

…

     FROM employee, skills

…

Example : Specifying a TABLE Function in the FROM Clause

The following statement inserts all of the rows that the sales_retrieve table function produces into salestable:

     INSERT INTO salestable

     SELECT s.store, s.item, s.quantity

     FROM TABLE (sales_retrieve(9005)) AS s;

For More Information

For information about:

How to format a HASH BY clause, see “HASH BY Clause” on page 109.

How to format a LOCAL ORDER BY clause, see “LOCAL ORDER BY Clause” on page 113.

Using the FROM clause with temporal tables, see “FROM Clause” in Temporal Table Support.

Self‑joins, see “Self‑Join” on page 252.

Outer joins, see “Outer Joins” on page 253.

Table functions, see “CREATE FUNCTION (Table Form)” in SQL Data Definition Language.

How to qualify column and table specifications, see SQL Fundamentals.