Details
Description
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.
Attachments
Issue Links
- is duplicated by
-
IMPALA-11074 Elimination of outer joins, if the predicates can restrict the result sets so that all null-supplying rows are eliminated
-
- Closed
-
- relates to
-
IMPALA-10382 Predicate with coalesce on both sides of LOJ isn't NULL filtering
-
- Resolved
-
-
-
- Resolved
-