DEV Community

Jessica Aki
Jessica Aki

Posted on

Old Oracle Joins vs ANSI JOINs: Why I Chose One and Stuck With It

#oracle #sql #database #beginners

When I first encountered Oracle’s old join syntax using the (+) operator, I was confused — not because joins were new to me, but because the way they were written felt unintuitive compared to what I had already learned with ANSI SQL.

So instead of treating this as “just another syntax,” I decided to properly compare old Oracle joins vs ANSI joins, understand why both exist, and then make a deliberate choice about which one I would personally stick with.

This post is that comparison.

The Two Ways to Write Joins in Oracle

Oracle supports:

  1. Old-style Oracle joins using the (+) operator
  2. ANSI-standard joins using LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

Both work. Both return results. But they feel very different when you read and maintain them.

Example Scenario

Let’s say we have:

  • clients → people who place orders
  • orders → what those clients buy

Some clients may not have placed any orders yet.

Old Oracle LEFT OUTER JOIN (+) 

SELECT c.client_id, c.name, o.order_id
FROM clients c, orders o
WHERE c.client_id = o.client_id(+);
Enter fullscreen mode Exit fullscreen mode

What’s happening here?

  • The (+) tells Oracle to preserve rows from clients
  • orders is optional
  • The join logic is mixed into the WHERE clause

The problem

  • It’s not visually obvious which table is optional
  • Adding filters can silently turn your outer join into an inner join
  • This syntax only works in Oracle

ANSI LEFT JOIN (The Same Query) 

SELECT c.client_id, c.name, o.order_id
FROM clients c
LEFT JOIN orders o
  ON c.client_id = o.client_id;
Enter fullscreen mode Exit fullscreen mode

Why this reads better

  • Join logic is separated from filters
  • You can instantly see which table is optional
  • The query reads almost like English

“Give me all clients, and their orders if they exist.”

FULL OUTER JOIN: The Big Difference

ANSI syntax 

SELECT c.client_id, c.name, o.order_id
FROM clients c
FULL OUTER JOIN orders o
  ON c.client_id = o.client_id;
Enter fullscreen mode Exit fullscreen mode

Clear. Explicit. Done.

Old Oracle syntax

Oracle doesn’t support FULL OUTER JOIN with (+).
You must simulate it using:

  • a LEFT JOIN
  • a RIGHT JOIN
  • and a UNION

This quickly becomes verbose and error-prone.

Portability Matters

ANSI joins:

  • Work in PostgreSQL, MySQL, SQL Server, Oracle, BigQuery
  • Are easier to transfer between jobs and databases
  • Are what most modern documentation and examples use

Old Oracle joins:

  • Lock you into Oracle
  • Appear mostly in legacy codebases
  • Are harder for non-Oracle developers to read

Why I Personally Chose ANSI JOINs

After using both, I made a deliberate choice:

  • ANSI joins are universal
  • They are easier to read, write, and reason about
  • They scale better as queries grow
  • They reduce accidental bugs caused by misplaced filters

Even when working with Oracle, I stick to ANSI syntax unless I’m forced to read legacy code.

Consistency matters more than preference — and ANSI gives me consistency across databases.

Final Thought

Knowing the old Oracle syntax is useful — especially when dealing with legacy systems.
But for new queries, learning projects, and cross-database skills, ANSI joins are the clear winner for me.

Learning SQL isn’t just about knowing what works — it’s about choosing what scales with you.

Jessica Aki
Data & Database Engineering Enthusiast

Top comments (0)