SQLMentor
Oracle (+) Outer Join Syntax Explained
Oracle's pre-ANSI (+) notation for outer joins still turns up in older code and on certification exams. Here's how to read it, its direct ANSI JOIN equivalent, and why you shouldn't write new code with it.
Oracle's original, pre-ANSI outer join syntax
Before the ANSI JOIN ... ON syntax became standard practice, Oracle used a proprietary notation: putting (+) next to a column in the WHERE clause to mark that side as "optional" — the side that gets NULL-extended when there's no match. It still appears in older Oracle code and occasionally on certification exams, even though ANSI join syntax is the modern, recommended standard.
The (+) syntax and its ANSI equivalent
Both queries return the same result — every employee, with department_name if one exists.
-- Old Oracle syntax: (+) marks departments as the "optional" side
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
-- ANSI equivalent (recommended)
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;How to read the (+)
The rule of thumb: the table WITHOUT the (+) is the one that keeps all its rows — the (+) marks the side that's allowed to be missing.
| Placement | Meaning |
|---|---|
| (+) on the right table's column | left table is the "driving" side — equivalent to a LEFT JOIN |
| (+) on the left table's column | right table is the "driving" side — equivalent to a RIGHT JOIN |
Restrictions that don't apply to ANSI syntax
The (+) syntax has real limitations ANSI joins don't share: you can't use it to express a full outer join (there's no way to mark both sides optional in the same condition — a separate UNION of two outer queries was the old workaround), it can't be combined with an OR across different columns in some cases, and mixing it with subqueries or complex conditions gets error-prone fast. This — plus plain readability — is why ANSI JOIN syntax has been the recommended standard in Oracle for a long time; new code should use it.