Skip to main content

Conditions should not discard the rows an outer join adds

ZPA rule for PL/SQL and Oracle SQL · OuterJoinDiscardedByCondition

On the rows an outer join adds for a source without a matching row, every column of that source is null. A condition that is never true for a null column of that source, written in the WHERE or in the ON of a later inner join, removes exactly those rows: the outer join then gives the same rows as an inner join. The code runs, but the rows without a match disappear.

Comparisons, LIKE, BETWEEN, IN with a list and IS NOT NULL on a column of the outer-joined source are reported when they apply to the whole query: alone or joined to the rest of the condition by AND. A condition that keeps the null rows, such as IS NULL, NVL or an OR with IS NULL, is not reported. In the old join syntax, a condition whose column is marked (+) belongs to the outer join and is not reported either.

Examples​

Noncompliant Code Example
select count(*)
into n
from shelf s
left join crate c on c.shelf_id = s.id
where c.color = 'RED'; -- Noncompliant
Compliant Solution
select count(*)
into n
from shelf s
left join crate c on c.shelf_id = s.id and c.color = 'RED';