Outer Join Syntax | SQL Joins | Teradata Vantage - 17.05 - Definition of the Outer Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The outer join is an extension of the inner join. The difference between outer joins and inner joins is the existence of an nonmatching row set in the outer join result.

Outer joins of two or more tables perform an inner join of those tables according to a specified join condition and also return rows from the left join table, the right join table, or both, that do not match the inner join condition, extending the results rows with nulls in the nonmatching fields.

The nonmatching rows are for those rows in the outer table that do not have a match in the inner table, while the matching rows in the outer join result are the rows that satisfy the join condition, which are exactly the same as those in the inner join result.

The outer join is generally defined as the algebraic UNION ALL of the components. UNION ALL permits duplicates and so is not a relational operator in the strict sense of the term.

Example: SELECT with Join

For example, consider the following SELECT statement:

     SELECT offerings.course_no, offerings.location, enrollment.emp_no
     FROM offerings
     LEFT OUTER JOIN enrollment
     ON offerings.course_no = employee.course_no;

where:

Object Corresponding Syntax Element
offerings left_table
enrollment right_table
offerings.course_no=employee.course_no join_condition

Rule for Specifying a SAMPLE Clause in an ON Clause Subquery

You cannot specify a SAMPLE clause in a subquery used as an ON clause predicate.

Simple Outer Join Example

Assume you have the data presented in tables t1 and t2:

t1   t2
FK     PK  
x1 y1   x2 y2
1 1   1 1
2 2   3 3
3 3   4 4
4 4   5 5

An inner join of these tables is specified by the following query:

     SELECT x1, y1, x2, y2
     FROM t1, t2
     WHERE x1=x2;
x1 y1 x2 y2
1 1 1 1
3 3 3 3
4 4 4 4

An left outer join of these tables is specified by the following query:

     SELECT x1, y1, x2, y2
     FROM t1 LEFT OUTER JOIN t2 ON x1=x2;

The result of this query is as follows:

x1 y1 x2 y2
1 1 1 1
2 2 Null Null
3 3 3 3
4 4 4 4

Outer Join Is Equivalent to Inner Join for Selected Foreign Key-Primary Key Relationships

In the original table definitions, x2 is the primary key of t2. Assume the following referential integrity constraint is defined on t1:

     FOREIGN KEY (x1) REFERENCES t2 (x2);

This constraint requires each of the x1 (foreign key) values in t1 to also exist as the primary key for one, and only one, row in table t2 as column x2. Therefore the second row in t1 cannot be valid, which makes the nonmatching row in the outer join result disappear from the outcome. See Simple Outer Join Example. The outer join result converts into the inner join result on the same tables because the referential integrity constraint guarantees there can be no unmatched rows in the join result.

Mathematically, the foreign key-primary key relationship implies that the values in the foreign key column set form a proper subset of the set of values in the primary key column set, expressed mathematically as S FK ⊂ S PK, where S FK represents the foreign key value set and S PK represents the primary key value set.

Therefore, an equijoin between the parent table primary key column set and a child table foreign key column set always leaves the unmatched row set empty so that an outer join is equivalent to the corresponding inner join. This property is extremely valuable for query optimization using join indexes. For more information, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 and Teradata Vantage™ - Database Design, B035-1094.

Components of an Outer Join

Refer to the following table abstractions and Venn diagram.



This diagram is the same as Components of an Inner Join. However, the way the components of this diagram are combined is very different.

Define table_A as the row set contained by sections 1 and 2. Refer to table_A as left_table.

Define table_B as the row set contained by sections 1 and 3. Refer to table_B as right_table.

Define the components of a 2-table outer join as follows:

Component Definition
1 Inner join of the 2 tables as defined by an ON clause with all join conditions applied. These rows also provide the matching rows of an outer join.
2 All rows from left_table not included in section 1 extended with nulls for each nonmatching column from left_table. These are the unmatched rows in a left outer join.
3 All rows from right_table not included in section 1 extended with nulls for each nonmatching column selected from right_table. These are the unmatched rows in a right outer join.


Types of Outer Join

The sections to be unioned are defined by the type of outer join:

Outer Join Type Definition
LEFT Section 1 UNION ALL Section 2
RIGHT Section 1 UNION ALL Section 3
FULL Section 1 UNION ALL Section 2 UNION ALL Section 3

For each type of outer join, consider the operation as assembling the proper components using the UNION ALL operator. Because UNION ALL permits duplicates, it is not a relational operator in the strict sense of the term.

Inner Table and Outer Table

The terms inner table and outer table, used frequently when writing about outer joins, are defined in the following table:

Term Definition
Inner table The inner table is the table that contributes only matched rows to the outer join result.

For an inner join, both tables are inner tables.

Outer table The outer table is the table that contributes unmatched rows to the outer join result.

In this description, unmatched refers to the rows in the left or right (or both) table that are not part of the inner join rows because there are no matching columns, so the rows are extended with nulls in the results table.

This terminology also applies to the result of nested joins and spools.

Inner/Outer Table Example

Consider the following nested outer join:

     (table_A
     LEFT OUTER JOIN
     (table_B
     RIGHT OUTER JOIN table_C ON join_condition
     )
     ON join_condition
     )
     FULL OUTER JOIN table_D ON join_condition

The inner and outer tables of this outer join are as follows.

Beginning with the most deeply nested join and working outward, these relationships apply:

  1. The table_c is an outer table with respect to table_b
  2. The table_a is an outer table with respect to the nested join (table_b RIGHT OUTER JOIN table_c ON join_condition)
  3. (table_a LEFT OUTER JOIN (table_b RIGHT OUTER JOIN table_c ON join_condition) ON join_condition is an outer table for the full outer join
  4. The table_d is an outer table for the full outer join

Projected Column List

When you construct an outer join, choose the projected column list carefully to ensure the results are useful and interpretable. In general, you should project the column from the same side as the outer join. Practical Example of a Right Outer Join right outer joins on CourseNo. Therefore, project out the right join column, which is Enrollment.CourseNo.

Similarly, in the full outer join in Practical Example of a Full Outer Join, the example projects both CourseNo columns for this same reason.

Nulls and the Outer Join

Nulls are a fundamental component of the report produced by a query that contains an outer join. The key feature of the outer join is that in returning rows from the outer table set, the report extends the rows that have no matching values with nulls, as if these unknown “values” came from the corresponding table.

For example, if you want to list courses offered by customer education for which employees have registered and also to include in that list those courses for which no one signed up.

The practical outer join examples in Left Outer Join, Right Outer Join, and Full Outer Join use these three tables.
  • The table offerings shows customer education courses currently being offered and their location. With respect to courses being offered, you can think of offerings as a subset of courses.
    offerings
    course_no beginning_dates location
    C100 01/05/2006 El Segundo
    C200 07/02/2006 Dayton
    C400 10/07/2006 El Segundo
  • The table enrollment shows employees who have registered for courses, some of which may not be offered.
    enrollment
    emp_no course_no
    236 C100
    236 C300
  • The table courses lists all courses developed by customer education, some of which are not currently being offered (for example, C300).
    courses
    course_no name
    C100 Recovery Planning
    C200 Software Architecture
    C300 Teradata Basics
    C400 Introduction to Java Programming

The nulls reported for an outer table set do not represent missing information, but the empty set. For a description of the various uses of nulls in the SQL language, see Teradata Vantage™ - Database Design, B035-1094.

Example: Left Outer Join

The following SELECT statement is an example of a left outer join of two tables, table_a and table_b:

     SELECT column_expression
     FROM table_a
     LEFT OUTER JOIN table_b ON join_conditions
     WHERE join_condition_exclusions;

The left table in the above example means the table specified to the left of the keywords OUTER JOIN in the FROM clause. The right table means the table specified to the right of the keywords OUTER JOIN.

The keyword LEFT indicates the source of the nonmatching rows returned in the result of the left outer join.

In addition to performing an inner join of two or more tables according to a join condition, a left outer join, as in the example above, returns nonmatching rows from its left table (table_a) and extends them with nulls.

A right outer join returns nonmatching rows from its right table and extends the rows with nulls.

A full outer join returns nonmatching rows from both of the join tables and extends the rows with nulls.

The reserved word OUTER is optional so that the preceding SELECT statement could also be written as follows:

     SELECT ...
     FROM table_A
     LEFT JOIN table_B ON join_condition;

Example: Scalar Subquery in the ON Clause of a Left Outer Join

You can specify a scalar subquery as an operand of a scalar predicate in the ON clause of an outer join specification.

The following example specifies a scalar subquery (SELECT AVG(price)…) in its ON clause.

     SELECT category, title, COUNT(*)
     FROM movie_titles AS t2 LEFT OUTER JOIN transactions AS txn
     ON (SELECT AVG(price) 
         FROM movie_titles AS t1
         WHERE t1.category = t2.category)<(SELECT AVG(price)
                                           FROM movie_titles)
         AND t2.title = txn.title;

Rules for Using the DEFAULT Function as a Search Condition for an Outer Join ON Clause

The following rules apply to the use of the DEFAULT function as part of the search condition within an outer join ON clause:
  • The DEFAULT function takes a single argument that identifies a relation column by name. The function evaluates to a value equal to the current default value for the column. For cases where the default value of the column is specified as a current built-in system function, the DEFAULT function evaluates to the current value of system variables at the time the request is processed.

    The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.

  • The DEFAULT function has two forms: DEFAULT or DEFAULT (column_name). When no column name is specified, the system derives the column based on context. If the column context cannot be derived, an error is returned to the requestor.
  • You can specify a DEFAULT function with a column name argument within a predicate. The system evaluates the DEFAULT function to the default value of the column specified as its argument. Once the system has evaluated the DEFAULT function, the function is calculated as a constant in the predicate.
  • You can specify a DEFAULT function without a column name argument within a predicate only if there is one column specification and one DEFAULT function as the terms on each side of the comparison operator within the expression.
  • Following existing comparison rules, a condition with a DEFAULT function used with comparison operators other than IS NULL or IS NOT NULL is unknown if the DEFAULT function evaluates to null.

    A condition other than IS NULL or IS NOT NULL with a DEFAULT function compared with a null evaluates to unknown.

DEFAULT Function and NULL Comparison Result
IS NULL
  • TRUE if the default is null
  • Otherwise FALSE
IS NOT NULL
  • FALSE if the default is null
  • Otherwise TRUE

For more information about the DEFAULT function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

Use of the DEFAULT Function in an Outer Join ON Condition

The examples assume the following table definitions:

     CREATE TABLE table15 (
       col1 INTEGER ,
       col2 INTEGER NOT NULL,
       col3 INTEGER NOT NULL DEFAULT NULL,
       col4 INTEGER CHECK (col4 > 10) DEFAULT 9 );
     CREATE TABLE table16 (
       col1 INTEGER,
       col2 INTEGER DEFAULT 10,
       col3 INTEGER DEFAULT 20,
       col4 CHARACTER(60)  );

You can specify a DEFAULT function as a component of the search condition for an outer join. The following examples demonstrate proper use of the DEFAULT function as part of an outer join search condition.

In the following example, the DEFAULT function evaluates to the default value of table16.col2, which is 10.

     SELECT *
     FROM table16
     FULL OUTER JOIN table15 ON table15.col1 < DEFAULT(table16.col2);

Therefore, the previous example is equivalent to the following example:

     SELECT *
     FROM table16
     FULL OUTER JOIN table15 ON table15.col1 < 10;