Outer join simplification

As a general rule, an outer join can be converted to an inner join if there is a condition on the inner table that filters out non‑matching rows. In a left outer join, the right table is the inner table, while it is the left table in a right outer join. In a full outer join, both tables are inner tables. Conditions that are FALSE for nulls are referred to as null filtering conditions, and these are the conditions that enable the outer‑to‑inner join conversion to be made.

An outer join can be converted to an inner join if at least one of the following conditions is true.

• The WHERE clause contains at least one null filtering condition on the inner table.
• The outer join is involved in another join, and the other join condition has one or more null filtering conditions on the inner table. The other join in this case can be an inner join, left outer join, or right outer join. It cannot be a full outer join because there is no inner table in this case.

A null filtering condition on the right side of a full outer join converts it to a left outer join, while a null filtering condition on the left side converts it to a right outer join.

For example the following query

select t1.c1, t2.c1
from t1 left outer join t2 using (x)
where t2.c2 > 5

can safely be converted to

select t1.c1, t2.c1
from t1 join t2 using (x)
where t2.c2 > 5

because the predicate t2.c2 > 5 is interpreted as FALSE if t2.c2 is NULL and therefore the condition removes all non‑matching rows of the outer join.