15.10 - JOIN - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

An introduction to the name of the second table to participate in the join.

joined_table
Name of the joined user base table, view, or derived table.
This can itself be a recursive reference in the recursive statement portion of the view definition. You cannot make a reference to a recursive view in the seed statement of the view definition.
You cannot create or replace a view on a queue table (see CREATE TABLE (Queue Table Form) ).
database_name
user_name
Containing database or user for joined_table if something other than the current database or user.
INNER
A join in which qualifying rows from one table are combined with qualifying rows from another table according to a specified join condition.
INNER is the form specified by the ANSI SQL-2011 standard. Teradata also supports an extension that allows you to separate join relations using COMMA characters.
Inner join is the default join type for view definitions.
OUTER
A join in which qualifying rows from one table or view that do not have matches in the other table or view, are included in the join result. The rows from the outer table or view are extended with nulls.
Outer joins are only valid selectively in recursive view definitions. See the individual outer join types for specific information.
See SQL Data Manipulation Language, B035-1146 for more information about outer joins.
LEFT OUTER
An outer join on the table or view that was listed first in the FROM clause.
In a LEFT OUTER JOIN, the rows from the left table or view that are not returned in the result of the inner join of the two tables/views are returned in the outer join result and extended with nulls.
A left outer join is valid when coded within a seed statement.
Left outer joins are only valid within a recursive statement when the recursive query reference is the inner, or left, table in the outer join definition.
RIGHT OUTER
An outer join on the table or view that was listed second in the FROM clause.
In a RIGHT OUTER JOIN, the rows from the right table or view that are not returned in the result of the inner join of the two tables/views are returned in the outer join result and extended with nulls.
A right outer join is valid when coded within a seed statement.
Right outer joins are only valid within a recursive statement when the recursive query reference is the outer, or right, table in the outer join definition.
FULL OUTER
A join that returns rows, including non-qualifying rows, from both tables or views.
In a FULL OUTER JOIN, 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.
Full outer joins are only valid when coded within a seed statement.
One or more conditional expressions that must be satisfied by the result rows. An ON condition clause is required if the FROM clause specifies outer join syntax.
You cannot specify a SAMPLE clause within a subquery predicate within an ON clause.