Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 90 91 92 93 94 95 < 96 > 97 98 99 100 101 102 .. 241 >> Next

AS aname a different, temporary name (alias) for the table that is referenced by tname. aname must be used during a self-join operation on the table. The use of AS introducing aname is optional.

Joined Tables

Joined tables options allows the FROM list to specify multiple tables, joined in explicit ways, as described below.

joined_table either (and usually is) 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. Types of inner joins include exclusion, merge, nested, product, and row ID. 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. The rows from the first table are extended with null values.
LEFT [OUTER] a signifier for the join type. LEFT indicates the table that was listed first in the FROM clause. In a LEFT OUTER JOIN, 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 null values.
RIGHT [OUTER] a signifier for the join type. RIGHT indicates the table that was listed second in the FROM clause. In a RIGHT OUTER JOIN, 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 null values.

Teradata RDBMS for UNIX SQL Reference

7-17
Queries: The SELECT Statement

FROM Clause

Syntax Element . . . Is . . .
FULL [OUTER] a signifier for the join type. FULL OUTER JOIN returns rows from both tables. In a FULL OUTER JOIN, rows from both tables that have not been returned in the result of the inner join, will be returned in the outer join result, and extended with null values.
CROSS JOIN a signifier for the join type. A CROSS JOIN is an unconstrained, or Cartesian join; it returns all rows from all tables specified in the FROM clause. Two joined tables can be CROSS joined.
ON search_cond one or more conditional expressions that must be satisfied by the result rows. An ON condition clause is required if the FROM clause specifies an outer join.

Derived Tables

The derived table allows the FROM list to specify a spool file, comprised of selected data from underlying table(s). The derived table acts like a viewed table.

subquery the subquery that defines the derived table contents.
AS derived_tname an assigned name for the temporary derived table. AS in an introductory optional keyword for derived table.
col_name a list of column names or expressions listed in the subquery. Allows referencing subquery columns by name.

The following provides rules for using the FROM list in queries:

During a self-join operation, related data selected from different rows of the same table is combined and returned as one row. The temporary table names that are defined using the FROM clause qualify different references to the same table columns.

When a FROM clause references both a permanent table and an alias name, whether a self-join is performed depends 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 alias table names, as shown in the following example.

Usage Notes

Self-Joins

FROM Clause Using Both a Permanent Table and an Alias Name

7-18

Teradata RDBMS for UNIX SQL Reference
Queries: The SELECT Statement

FROM Clause

Once an alias 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 may be a Cartesian self-join.

In the following example, table TAB1 is given the alias T; the subsequent use of TAB1 causes the Cartesian product.

SELECT *

FROM TAB1 T

WHERE TAB1.COL1 = 2;

How Column References Affect Self-Joins

The effect of column references on self-join processing is as follows (these rules apply only to cases where a single aliasing FROM clause is supplied). See also Table 7-1:

IF this column reference . . . Is . . . THEN a self-join is . . .
all unqualified (the preceding tablename or aliasname is omitted) not performed.
qualified (but the qualifiers reference only the alias name) not performed.
some, but not all qualified and the qualifiers reference only the alias name not performed.
qualified and the qualifiers reference only the permanent table name moot because no assumptions can be made about the owner of the unqualified columns. This condition producers the following error message, where n is a number indicating the position of the statement in the request. *** Failure 3809 Column 'columnname' is ambiguous. Statement# n, Info = 0

Teradata RDBMS for UNIX SQL Reference
Previous << 1 .. 90 91 92 93 94 95 < 96 > 97 98 99 100 101 102 .. 241 >> Next