G-3194
🆓Always specify INNER or OUTER within JOIN TO ONE.
Reason
Every join inside a JOIN TO ONE clause should explicitly specify either INNER JOIN or OUTER JOIN. When the join type is omitted, JOIN TO ONE defaults to an outer join, which differs from the common expectation that an unqualified ANSI JOIN represents an inner join. This difference can easily be overlooked and is especially important because an outer join preserves unmatched rows and produces null values for columns from the joined table.
Making the join type explicit communicates the intended semantics and highlights where downstream null handling may be required.
Example
Non-Compliant Example
select e.first_name || ' ' || e.last_name as employee_full_name
,e.salary
,d.department_name
,j.job_title
,mgr.first_name || ' ' || mgr.last_name as manager_full_name
,mgr.salary as manager_salary
from employees e
join to one (
departments d
,jobs j
,employees mgr on e.manager_id = mgr.employee_id
)
where e.salary > mgr.salary
or mgr.employee_id is null; | Line | Column | Message |
|---|---|---|
| 9 | 11 | Specify an OUTER or INNER JOIN clause for departments. |
| 10 | 11 | Specify an OUTER or INNER JOIN clause for jobs. |
| 11 | 11 | Specify an OUTER or INNER JOIN clause for employees. |
The join type for mgr is implicit. Readers may incorrectly assume an inner join and overlook that mgr columns can be null.
Compliant Solution - ★★★★★
select e.first_name || ' ' || e.last_name as employee_full_name
,e.salary
,d.department_name
,j.job_title
,mgr.first_name || ' ' || mgr.last_name as manager_full_name
,mgr.salary as manager_salary
from employees e
join to one (
outer join departments d
inner join jobs j
outer join employees mgr on e.manager_id = mgr.employee_id
)
where e.salary > mgr.salary
or mgr.employee_id is null; OUTER JOIN makes it clear that employees without a manager are preserved and that mgr.employee_id can be null, explaining the null check in the WHERE clause.