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;
|Object||Corresponding Syntax Element|
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:
|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:
|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:
- The table_c is an outer table with respect to table_b
- The table_a is an outer table with respect to the nested join (table_b RIGHT OUTER JOIN table_c ON join_condition)
- (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
- 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 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 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 NOT NULL||
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;