“A JOIN isn’t something you memorize. It’s something you reach for whenever one table doesn’t know enough.”

Most SQL tutorials start by explaining INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN as if they were different SQL features.

They’re not.

A JOIN is simply a way to connect information that lives in different tables.

The real question isn’t:

“Which JOIN should I use?”

It’s:

“What information am I missing, and do I want to keep rows that don’t have matching data?”

Throughout this article, we’ll use Oracle’s HR sample schema, which models a real company and provides perfect examples for every join type.

Understanding the HR Schema

Before writing any joins, it’s important to understand why tables are separated.

Oracle doesn’t store everything in one huge table.

Instead, each table owns one responsibility.

Think of each table as a specialist.

For example, the EMPLOYEES table knows an employee’s department ID, but it doesn’t know the department’s name.

EMPLOYEES

employee_id
first_name
last_name
department_id
job_id
manager_id

Notice that Oracle stores department_id, not Executive or IT.

Why?

Imagine the company decides to rename the Executive department.

If every employee stored the department name, Oracle would need to update hundreds or thousands of rows.

Instead, Oracle updates exactly one row in the DEPARTMENTS table.

This is called normalization, one of the core principles of relational database design.

Our Sample Data

We’ll use the following simplified data throughout this article.

EMPLOYEES

DEPARTMENTS

Notice three important things.

• Steven, Neena and Lex belong to Executive.
• Susan hasn’t been assigned to a department.
• Finance and Accounting currently have no employees.

This small dataset allows us to demonstrate every join type.

INNER JOIN

An INNER JOIN links two related tables together and returns only the rows that have matching values in both tables.

Most business databases are normalized, meaning information is split into multiple tables to avoid duplication. Instead of storing the same customer name or department name over and over again, the database stores it once and links to it using an ID.

An INNER JOIN is how we reconnect those pieces when we need a complete picture.

Employee → Department

In Oracle’s HR schema, the EMPLOYEES table doesn’t store department names.

Instead, it stores only the department’s ID.

EMPLOYEES

Meanwhile, the department information lives in another table.

DEPARTMENTS

To display each employee’s department name, Oracle needs to connect these two tables.

SELECT
    e.first_name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

The result becomes much more meaningful.

The department_id acts as the bridge between the two tables.

Invoice → Customer

A sales system works exactly the same way.

INVOICES

CUSTOMERS

The invoice table doesn’t repeat the customer’s name on every invoice. Instead, it stores only the customer_id.

To generate a readable invoice report, we join the two tables.

SELECT
    i.invoice_id,
    c.customer_name,
    i.amount
FROM invoices i
INNER JOIN customers c
    ON i.customer_id = c.customer_id;

Now the report shows:

Without the join, you’d only see customer IDs, which aren’t very useful to people reading reports.

Product → Category

An online store may have thousands of products, but only a few dozen categories.

Instead of storing the word Electronics on every product, each product stores only a category_id.

Products
-------------
product_id
name
category_id

Categories
-------------
category_id
category_name

A join lets us display the category name alongside each product.

This saves storage space and makes updates much easier. If the category name changes from Cell Phones to Mobile Phones, you only update one row in the CATEGORIES table.

Use INNER JOIN when…

• Looking up customer names for invoices
• Displaying each employee’s department
• Showing a product’s category
• Finding the customer who placed an order
• Displaying a student’s enrolled course
• Showing an employee’s job title

In all of these examples, the two tables are connected by a shared identifier.

ANSI JOIN vs Oracle Legacy Syntax

If you’ve worked with older Oracle applications, you’ve probably seen this:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

Modern SQL writes the same query as:

SELECT
    e.first_name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Both queries return exactly the same result.

The difference is readability.

ANSI syntax separates:

• ON → How tables are connected.
• WHERE → Which rows should be filtered.

As queries become larger, this separation makes SQL much easier to understand.

Now that we understand how an INNER JOIN connects related data, let’s answer a different question.

What happens when there isn’t a matching row?

Should Oracle discard the row?

Or should it keep it?

That’s exactly what LEFT JOIN and RIGHT JOIN help us decide.

LEFT JOIN

A LEFT JOIN keeps all rows from the left table, even if no matching row exists in the right table.

Unlike an INNER JOIN, rows without a match are not discarded. Instead, Oracle fills the columns from the right table with NULL.

Business Question

Show every employee, even if they haven’t been assigned to a department yet.

Suppose our sample data looks like this.

EMPLOYEES

DEPARTMENTS

Notice that Susan hasn’t been assigned to a department yet.

If we use an INNER JOIN:

SELECT
    e.first_name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Susan disappears from the result because Oracle can’t find a matching department.

However, HR asked for every employee, not just employees with valid departments.

Using a LEFT JOIN solves that problem.

SELECT
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

Result

Susan is still listed.

Oracle simply reports that it doesn’t know her department yet.

Why use a LEFT JOIN?

The key isn’t the SQL syntax—it’s the business requirement.

An INNER JOIN answers:

Show me employees with departments.

A LEFT JOIN answers:

Show me all employees, and if they have a department, include it.

The left table is considered the master list.

Oracle promises not to lose any rows from that table.

Real-world use cases

Notice the pattern.

The table on the left contains the records you don’t want to lose.

Finding missing data

One of the most common uses of a LEFT JOIN is finding records that don’t have a match.

Suppose HR asks:

Which employees haven’t been assigned to a department?

SELECT
    e.first_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Result

This is one of the most practical uses of a LEFT JOIN.

You’re not just joining tables.

You’re asking Oracle to show me what’s missing.

The takeaway

An INNER JOIN throws away rows that don’t match. A LEFT JOIN keeps them.

Or, even better:

Use a LEFT JOIN when the left table is your master list and every row on that list matters, even if related information is missing.

RIGHT JOIN

A RIGHT JOIN keeps all rows from the right table, even if no matching row exists in the left table.

Think of it as the mirror image of a LEFT JOIN.

The only question you need to ask is:

Which table do I refuse to lose rows from?

If the answer is the right table, use a RIGHT JOIN.

Business Question

Show every department, even if no employees work there.

Using the same sample data:

EMPLOYEES

DEPARTMENTS

Notice that Finance and Accounting currently have no employees.

An INNER JOIN hides those departments.

SELECT
    d.department_name,
    e.first_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Finance and Accounting disappear.

But HR asked for every department, including empty ones.

A RIGHT JOIN preserves the departments.

SELECT
    d.department_name,
    e.first_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

Result

The departments remain.

Oracle simply reports that no employee belongs to them.

Why use a RIGHT JOIN?

Exactly the same reason you’d use a LEFT JOIN.

The only difference is which table is the master list.

A LEFT JOIN says:

Keep every employee.

A RIGHT JOIN says:

Keep every department.

Nothing else changes.

Finding missing data

Suppose HR asks:

Which departments don’t have any employees?

SELECT
    d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

Result

Should I use RIGHT JOIN?

Everything you can write with a RIGHT JOIN can also be written as a LEFT JOIN by swapping the table order.

For example:

SELECT
    d.department_name,
    e.first_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

is equivalent to:

SELECT
    d.department_name,
    e.first_name
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id;

Many developers prefer the second version because the table they want to preserve appears first.

The takeaway

RIGHT JOIN doesn’t do anything that LEFT JOIN can’t. It simply lets you choose to preserve the right table instead of the left.

That explains why it’s part of SQL while also explaining why you don’t see it as often in production code.

We learned an important lesson:

Every join answers one simple question:

“Which rows do I want to keep?”

• INNER JOIN → Keep only matching rows.
• LEFT JOIN → Keep every row from the left table.
• RIGHT JOIN → Keep every row from the right table.

Now let’s look at three joins that solve completely different problems.

• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN

FULL OUTER JOIN

A FULL OUTER JOIN keeps every row from both tables.

• Matching rows are combined.
• Rows that exist only in the left table are kept.
• Rows that exist only in the right table are also kept.

If Oracle can’t find a match, it fills the missing columns with NULL.

Think of it as combining a LEFT JOIN and a RIGHT JOIN.

LEFT JOIN
+
RIGHT JOIN
=
FULL OUTER JOIN

Business Question

Compare employees and departments without losing anything.

We’ll use the same sample data.

EMPLOYEES

DEPARTMENTS

Notice:

• Susan doesn’t belong to a department.
• Finance has no employees.
• Accounting has no employees.

Suppose HR wants one report showing everything.

SELECT
    e.first_name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

Result

Nothing is discarded.

Everyone appears exactly once.

Why use a FULL OUTER JOIN?

Unlike the previous joins, a FULL OUTER JOIN isn’t about choosing which table to preserve.

It’s about saying:

Both tables are equally important.

You don’t want Oracle to hide anything.

Real-world use cases

A FULL OUTER JOIN is most useful when comparing two sets of data.

Notice something?

These aren’t everyday reporting queries.

They’re comparison or verification tasks.

Data migration example

Imagine your company is replacing its payroll system.

Before going live, you want to verify that every employee exists in both systems.

Legacy Payroll

New Payroll

A FULL OUTER JOIN immediately reveals:

Now you know:

• Lex wasn’t migrated.
• Bruce exists only in the new system.

That’s exactly what auditors and migration teams need.

Finding differences

A common pattern is to combine a FULL OUTER JOIN with a WHERE clause.

For example, to show only the rows that don’t match:

SELECT
    e.first_name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id
WHERE e.employee_id IS NULL
   OR d.department_id IS NULL;

Result

Instead of showing everything, Oracle now shows only the exceptions.

This is a common technique when validating data.

Do you use FULL OUTER JOIN often?

Not really.

Most day-to-day business applications use:

• INNER JOIN
• LEFT JOIN

A FULL OUTER JOIN is more specialized.

You’ll most often see it in:

• Data migration projects
• Database synchronization
• Auditing
• Data quality checks
• Comparing reports from different systems

The takeaway

Use a FULL OUTER JOIN when you need to compare two lists and neither list is allowed to disappear.

Unlike INNER, LEFT, or RIGHT, you’re not choosing which table is more important.

You’re telling Oracle:

Keep everything. I’ll decide later what I want to do with the unmatched rows.

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from two tables.

Unlike the other joins, a CROSS JOIN doesn’t require a matching column.

There is no ON clause because Oracle isn’t trying to find related records.

Instead, Oracle simply combines every row from the first table with every row from the second table.

Business Question

Generate every possible employee and department combination.

Using our sample data:

EMPLOYEES

DEPARTMENTS

A CROSS JOIN returns:

Notice that Steven doesn’t actually work in IT.

That’s not the point.

A CROSS JOIN isn’t looking for relationships.

It’s generating every possible combination.

How does Oracle calculate the result?

The number of rows returned is simply:

Rows in first table × Rows in second table

For our sample:

3 employees × 2 departments = 6 rows

If your tables contain:

• 100 employees
• 20 departments

Oracle returns:

100 × 20 = 2,000 rows

If they contain:

• 10,000 products
• 500 stores

Oracle returns:

10,000 × 500 = 5,000,000 rows

This is why a CROSS JOIN can become expensive very quickly.

Real-world use cases

Unlike the other joins, a CROSS JOIN isn’t used to connect related tables.

Instead, it’s used to generate combinations.

Notice the pattern.

We’re not asking:

Which records match?

We’re asking:

What are all the possible combinations?

Example: Product catalog

Suppose an online clothing store sells:

COLORS

SIZES

A CROSS JOIN generates every product variation.

SELECT
    c.color,
    s.size
FROM colors c
CROSS JOIN sizes s;

Result

Instead of typing six rows manually, Oracle creates every combination automatically.

A common mistake

Sometimes developers accidentally write a CROSS JOIN.

For example:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
     departments d;

Notice what’s missing?

There’s no join condition.

Oracle doesn’t know how the tables are related.

So it assumes:

Combine every employee with every department.

This is called a Cartesian product.

If there are:

• 107 employees
• 27 departments

Oracle returns:

107 × 27 = 2,889 rows

Most of those rows are meaningless.

This is one of the most common SQL mistakes made by beginners.

The takeaway

Unlike every other join we’ve discussed, a CROSS JOIN doesn’t connect related data.

Instead, it creates every possible pairing between two tables.

That’s incredibly useful when generating combinations—but potentially disastrous if done accidentally.

The question to ask yourself is:

Do I want matching records, or do I want every possible combination?

If the answer is every possible combination, a CROSS JOIN is exactly the right tool.

Otherwise, you almost certainly want one of the other join types.

SELF JOIN

A SELF JOIN joins a table to itself.

That might sound strange at first, but it’s actually very common.

Sometimes a row needs to reference another row in the same table.

Instead of creating another table, the database stores a relationship between rows.

Business Question

Show every employee and their manager.

The Oracle HR schema is a perfect example.

The EMPLOYEES table stores both employees and managers.

Managers aren’t stored in a separate table—they’re simply employees with a different role.

EMPLOYEES

Notice something interesting.

The manager_id doesn’t store a manager’s name.

It stores another employee_id.

For example:

• Neena’s manager_id is 100.
• Employee 100 is Steven.

The relationship is entirely within the same table.

How do we connect them?

Since both employees and managers come from the EMPLOYEES table, we reference it twice using different aliases.

SELECT
    e.first_name AS employee,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Notice the aliases.

• e represents the employee.
• m represents the manager.

Even though both refer to the same table, Oracle treats them as two different roles.

Why use a LEFT JOIN?

Because the CEO doesn’t report to anyone.

If we used an INNER JOIN, Steven would disappear because there isn’t a matching manager.

A LEFT JOIN keeps every employee, even if they don’t have a manager.

Real-world use cases

Notice the pattern.

The relationship isn’t between two different tables.

It’s between two rows in the same table.

The takeaway

A SELF JOIN doesn’t require a special SQL keyword.

It’s simply a normal join where the same table appears twice.

The important idea isn’t the syntax.

It’s recognizing when a table contains a relationship to itself.

When one row points to another row in the same table, a self join lets you follow that relationship.

Summary

At this point, you’ve learned all six major SQL joins.

Notice that none of these joins are difficult once you stop thinking about syntax and start thinking about what business question you’re trying to answer.

Reading Legacy Oracle JOIN Syntax

If you’re maintaining an older Oracle application, you’ll almost certainly encounter SQL like this:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id(+);

At first glance, it looks completely different from the ANSI JOINs we’ve learned.

Fortunately, it isn’t.

It’s simply Oracle’s older join syntax.

Once you know how to translate it, you’ll discover it’s expressing exactly the same ideas.

Why does this syntax exist?

Before Oracle supported ANSI SQL joins, Oracle developers wrote joins using the WHERE clause.

Instead of writing

LEFT JOIN departments

they wrote

WHERE employees.department_id = departments.department_id(+)

This syntax appeared in Oracle long before ANSI SQL became the industry standard.

That’s why many mission-critical Oracle systems still contain it today.

If you’re working in banking, insurance, manufacturing, telecom, healthcare, or government, there’s a good chance you’ll see this style every day.

Legacy INNER JOIN

Suppose we want to display each employee and their department.

Legacy Oracle syntax:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

ANSI SQL:

SELECT
    e.first_name,
    d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

These queries return exactly the same result.

The only difference is where the join condition is written.

Legacy LEFT JOIN

This is probably the most common (+) syntax you’ll encounter.

Legacy Oracle:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id(+);

ANSI SQL:

SELECT
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

The result is identical.

How do I read (+)?

This is the trick I wish someone had taught me years ago.

The (+) belongs to the optional table.

In this example,

e.department_id = d.department_id(+)

the (+) is attached to departments.

That tells Oracle:

Keep every employee.

If a department exists, join it.

Otherwise, return NULL.

That’s exactly how a LEFT JOIN behaves.

Legacy RIGHT JOIN

Now move the (+).

Legacy Oracle:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
     departments d
WHERE e.department_id(+) = d.department_id;

ANSI SQL:

SELECT
    e.first_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

Notice that the (+) is now attached to employees.

Oracle is saying:

Keep every department.

Employees become optional.

Exactly like a RIGHT JOIN.

FULL OUTER JOIN

One limitation of Oracle’s old syntax is that it cannot express a FULL OUTER JOIN directly.

Developers usually wrote something like this:

SELECT ...
FROM ...
WHERE a.id = b.id(+)

UNION

SELECT ...
FROM ...
WHERE a.id(+) = b.id;

Modern SQL is much simpler.

SELECT
    e.first_name,
    d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

Joining to an Inline View

Another pattern you’ll see constantly is joining to a subquery.

Legacy Oracle:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
(
    SELECT department_id,
           department_name
    FROM departments
    WHERE location_id = 1700
) d
WHERE e.department_id = d.department_id;

Many beginners think the subquery changes how the join works.

It doesn’t.

The subquery simply produces a temporary result set.

Oracle then joins to it exactly like any other table.

ANSI SQL:

SELECT
    e.first_name,
    d.department_name
FROM employees e
INNER JOIN
(
    SELECT department_id,
           department_name
    FROM departments
    WHERE location_id = 1700
) d
ON e.department_id = d.department_id;

Exactly the same logic.

LEFT JOIN with an Inline View

The same rule applies.

Legacy Oracle:

SELECT
    e.first_name,
    d.department_name
FROM employees e,
(
    SELECT department_id,
           department_name
    FROM departments
    WHERE location_id = 1700
) d
WHERE e.department_id = d.department_id(+);

ANSI SQL:

SELECT
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN
(
    SELECT department_id,
           department_name
    FROM departments
    WHERE location_id = 1700
) d
ON e.department_id = d.department_id;

Again, the (+) simply becomes a LEFT JOIN.

My Mental Translation

When I read old Oracle SQL, I don’t read it from top to bottom.

I mentally translate it.

For example:

SELECT
    e.first_name,
    j.job_title,
    d.department_name,
    l.city,
    c.country_name
FROM employees e,
     jobs j,
     departments d,
     locations l,
     countries c
WHERE e.job_id = j.job_id
AND e.department_id = d.department_id(+)
AND d.location_id = l.location_id(+)
AND l.country_id = c.country_id(+)
AND e.salary > 10000;

Instead of trying to understand everything at once, I separate the query into two groups.

Step 1: Find the joins

e.job_id = j.job_id

e.department_id = d.department_id(+)

d.location_id = l.location_id(+)

l.country_id = c.country_id(+)

Step 2: Find the business filters

e.salary > 10000

Now I mentally rewrite it.

SELECT
    e.first_name,
    j.job_title,
    d.department_name,
    l.city,
    c.country_name
FROM employees e
INNER JOIN jobs j
    ON e.job_id = j.job_id
LEFT JOIN departments d
    ON e.department_id = d.department_id
LEFT JOIN locations l
    ON d.location_id = l.location_id
LEFT JOIN countries c
    ON l.country_id = c.country_id
WHERE e.salary > 10000;

Suddenly the query becomes much easier to understand.

Oracle Translation Cheat Sheet

Final Thoughts

SQL joins are often taught as syntax.

In reality, they’re about relationships.

Every join answers one simple business question.

And if you’re an Oracle developer, there’s one more skill worth learning:

Be fluent in both ANSI SQL and Oracle’s legacy join syntax.

You don’t have to write new code using (+), but you’ll almost certainly encounter it when maintaining long-lived enterprise systems. Once you understand how to translate it mentally, legacy Oracle SQL becomes much less intimidating.