Outer Join Syntax | SQL Joins | Teradata Vantage - Definition of the Outer Join - Analytics Database - Teradata Vantage

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:

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:

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

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

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:

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:

Types of Outer Join

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

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:

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 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 preceding 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 previous example, 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

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;