Left Outer Join using + sign in Oracle 11g

sql

join

sql-syntax

best-practices

byAlex Kataev·Nov 27, 2024

Performing a left outer join in Oracle 11g with the + operator minimally changes the WHERE clause: place the + sign next to the optional table. If you prefer a modern, standard protocol, opt for ANSI LEFT JOIN syntax.

Using the + operator:

SELECT e.name, d.name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); -- Oracle version of "make it optional"

And in ANSI style:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id; -- ANSI style, because let's be explicit

Yielding the same result, the queries both return a list of employees matched with their departments, even if they're in the funky un-departmented crew. The LEFT JOIN is generally a safer pick for its cross-compatibility.

Mastering Join Syntax

Comparing Oracle and ANSI Syntax

The Oracle-specific (+) syntax and ANSI JOIN syntax do the same job but like twins, they've got different personalities. Both will perform a join, but they dress differently for the occasion:

The often-seen-in-the-wild Oracle:

SELECT e.name, d.name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+); -- Covert operations style

The straight-talking ANSI:

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id; -- No secrets with ANSI

ANSI is generally the cool twin these days, with more software deciding they're the life of the party.

Picking the Right Join for the Job

Choosing the correct join type can feel like picking a fork in a multi-course meal:

If all records from the main table need to be included, stick with a left outer join.
A right outer join is basically the left join's reflection. Most often, you can flip a right join and make it work as a left join by flipping the order of tables.

Avoid Combining All The Things!

Keeping your column mapping accurate in join conditions means you avoid the Icarus mistake of joining everything. That's a rookie mistake that leads to Cartesian products or missing out critical data.

When code clarity is no joke

When we're not joking around with our brunch invites, explicit JOIN syntax really does the job. It makes understanding the logic of your joins in SQL that much easier. And remember, in bigger queries with more tables, use ANSI JOIN syntax and everyone reading your code will thank you!