Outer joins

This section shows how to create and use outer joins in a SELECT statement. The topic Create a join discusses inner joins. Whereas an inner join treats two or more joined tables equally, an outer join treats two or more joined tables asymmetrically. An outer join makes one of the tables dominant (also called the outer table) over the other subordinate tables (also called inner tables).

In an inner join or in a simple join, the result contains only the combinations of rows that satisfy the join conditions. Rows that do not satisfy the join conditions are discarded.

In an outer join, the result contains the combinations of rows that satisfy the join conditions and the rows from the dominant table that would otherwise be discarded because no matching row was found in the subordinate table. The rows from the dominant table that do not have matching rows in the subordinate table contain NULL values in the columns selected from the subordinate table.

An outer join allows you to apply join filters to the inner table before the join condition is applied.

The database server supports syntax for outer joins that is an extension to the ANSI standard for the SQL language. Besides outer join syntax based on this HCL® OneDB® extension, the database server also supports the ANSI standard syntax, which provides more flexibility for creating queries that join a dominant table with one or more subordinate tables. With the exception of joins in view definitions, it is recommended that you use the ANSI standard syntax for creating new outer-join queries.

In view definitions, however, the HCL OneDB-extension syntax does not require materialized views, and so it might offer performance advantages over ISO/ANSI join syntax in those contexts, including business-analytic operations that query complex views joining multiple tables.

Whichever form of outer-join syntax you use, however, a single query cannot mix both syntax modes. All of the outer join operations in the same query block must either use SQL syntax that complies with the ISO/ANSI standard, or else use the HCL OneDB extension syntax.

Important: Before you rely on outer joins, determine whether one or more inner joins can work. You can often use an inner join when you do not need supplemental information from other tables.

Restriction: You cannot combine HCL OneDB and ANSI outer-join syntax in the same query block.

For information on the syntax of outer joins, see the HCL OneDB Guide to SQL: Syntax.