Difference between Oracle's plus (+) notation and ANSI JOIN notation?

sql

join

best-practices

performance

byNikita Barsukov·Jan 17, 2025

Oracle's (+) notation is a proprietary, dated method for expressing outer joins. The current universal standard is the ANSI JOIN, broadly adopted due to its lucidity and flexibility.

Key differences:

Legibility: ANSI JOINs specify the join type using keywords like LEFT, RIGHT, and FULL.
Versatility: ANSI JOINs can express a FULL OUTER JOIN, an operation impossible with (+) notation.

Here's a comparison using a left outer join:

Oracle's method - a little old school:

-- Your old buddy Oracle (+) wants to join the party
SELECT * 
FROM table1, table2 
WHERE table1.id = table2.id(+);

ANSI's method - clausal and expressive:

-- ANSI: "Have no fear, ANSI JOIN is here!"
SELECT * 
FROM table1 
LEFT OUTER JOIN table2 
ON table1.id = table2.id;

Migrate to ANSI JOINs for legibility and cross-platform compatibility.

Dive deeper: Oracle (+) vs ANSI JOIN

Enhanced complexity and multiple-table join with ANSI JOIN

ANSI JOIN syntax simplifies the execution of multi-table joins and complex join conditions. It maintains a structured, declarative methodology in scenarios where multiple tables need to be joined based on different conditions.

Example:

-- ANSI JOINs: "Why stop at one join when we can do so much more!"
SELECT *
FROM table1
LEFT OUTER JOIN table2 ON table1.id = table2.id
INNER JOIN table3 ON table2.id = table3.id;

ANSI JOINs: A step-up in outer join possibilities

The (+) notation of Oracle restricts you to single column joins and offers no support for full outer joins or arbitrary expressions. ANSI syntax conveniently provides these complex join mechanisms, granting you greater flexibility and robustness in your SQL ventures.

ANSI JOINs for organized and less ambiguous subqueries

When dealing with subqueries, especially correlated ones, ANSI JOIN syntax outperforms (+) notation. The clear and unambiguous representation of the join condition offered by ANSI syntax optimizes your SQL queries' performance and effectiveness.

Troubleshooting and Trade-offs

The (+) symbol, if applied incorrectly, can lead to error-prone results or failure to achieve an outer join. ANSI syntax, with its demand for explicit join conditions, effectively reduces these errors. This precision is particularly beneficial in preventing accidental cartesian products in sophisticated queries.

More insights into performance and best practices

Historically, there was a false belief that (+) notation had a performance edge. However, extensive testing unveiled that there's no noticeable performance difference between ANSI JOINs and Oracle's (+) operator. Furthermore, Oracle's documentation suggests using ANSI JOINs for consistency and optimization as the optimizer is more mature for ANSI syntax.

Self-join in Oracle and ANSI syntax

Self-joins are feasible with both notations. Nonetheless, ANSI syntax offers a clearer framework for these scenarios, reducing room for error by properly distinguishing between the aliases.

Blinking signs of deprecation

Albeit Oracle has not officially deprecated the (+) notation, their documentation recommends developers to shift towards ANSI syntax for future development, subtly hinting at potential deprecation. Aligning with best practices ensures the longevity of your code.

Portability with standard ANSI syntax

Being a standardized syntax, ANSI JOIN is compatible across different databases. Writing your SQL queries in ANSI syntax guarantees their portability to other platforms with minimal changes required.

explain-codes / Sql / Difference between Oracle's plus (+) notation and ANSI JOIN notation?

Linked

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



Left Outer Join using + sign in Oracle 11g



Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?



How to use multiple WITH statements in one PostgreSQL query?



Multiple INNER JOIN SQL ACCESS

