Left Outer Join (LOJ)

A Left Outer Join (LOJ) is one of the join operations that allows you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the second (right) table. This means all left rows that do not have a matching row in the right table will appear in the result, paired with a NULL value in place of a right row.

Syntax of LOJ

loj_from_clause ::= FROM ( aliased_table_name | left_outer_join_tables
left_outer_join_table ::= LEFT OUTER JOIN single_from_table ON expression
left_outer_join_tables ::= single_from_table left_outer_join_table (left_outer_join_table)*

Semantics

The FROM clause specifies the participating tables and separates them into two groups. First, the target table (the table which is on the left side of the LEFT OUTER JOIN clause) is specified. Then a LEFT OUTER JOIN clause can be specified. The table to the left of the LEFT OUTER JOIN keywords is called the left table, and the one to the right of LEFT OUTER JOIN is the right table. The name of any participating table in the join may be followed by a table alias. A table alias is a temporary name given to a table. Aliases are often used to make column names more readable. If no alias is specified, one is created internally, using the name of the table as it is spelled in the query, but with the dot(".") character replaced with '_' in the case of child tables. A join predicate specifies the columns on which records from two or more tables are joined. The expression after the ON clause lists all the join predicates between the two tables. The left_outer_join_tables clause specifies that the result of one LOJ can be the target table for another LOJ.

Like other kinds of joins, the LOJ creates a result set containing pairs of matching rows from the left and right tables. However, an LOJ will also preserve all rows of the left table, that is, a left row that does not have a matching row will appear in the result, paired with a NULL value in place of a right row.

Result set ordering in an LOJ:

In an LOJ, the order of fields in the result-set is always in top-down order. That means the order of output in the result set is always from the ancestor table first and then the descendant table. This is true irrespective of the order of the joins.