Oracle: What does `(+)` do in a WHERE clause?

sql

join

best-practices

sql-99

byAlex Kataev·Dec 11, 2024

In Oracle, (+) next to a column in a WHERE clause represents a left outer join. It returns all rows from the left table, along with matched rows from the right and nulls for non-matches. For clarity and modern compatibility, the universally-accepted ANSI LEFT OUTER JOIN syntax is recommended.

Old school Oracle (+) join:

-- "Yep, we're going old school Oracle style!"
SELECT e.name, d.name
FROM employees e, departments d
WHERE e.department_id = d.id(+);

Modern ANSI set-up:

-- "Embracing the future with ANSI style outer join!"
SELECT e.name, d.name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.id;

Embracing the ANSI Standard in the New Era of Databases

The ANSI SQL LEFT OUTER JOIN syntax enhances readability and maintainability while ensuring compatibility with major RDBMS. Uniform syntax lets us transition between different databases smoothly.

Ensuring Comprehensive Data: The Power of Outer Joins

A LEFT OUTER JOIN guarantees all rows from the left ("primary") table get included. Even with no corresponding match in the right ("joined") table, the rows appear with nulls for unmatched data.

Position Is Power: `(+)` Placement Matters

The position of (+) is key—it should be next to the column from the table that might have missing data. Pay attention to detail when placing (+)!

Embracing ANSI Standards with Oracle9i and Onward

Since Oracle9i, Oracle supports the ANSI SQL 99 OUTER JOIN syntax. For developers with legacy Oracle code or those working with different systems, it's valuable to grasp the ANSI JOIN syntax.

Avoiding the `(+)` Misinterpretation Trap

The (+) might lead to confusion due to its placement in the WHERE clause. The ANSI JOIN syntax keeps the join logic within the FROM clause, preventing it from getting mixed up with other query filters.