Oracle SQL JOINs Explained: From INNER JOIN to Full Outer Join

JOINs are the mechanism that makes relational databases relational. They let you combine data from multiple tables based on a common column — bringing together orders with customers, employees with departments, products with their categories. Understanding JOINs is arguably the most important skill in SQL, and Oracle SQL’s JOIN syntax is rich and powerful.

This guide covers every JOIN type in Oracle SQL with clear, practical examples and explains when to use each one.

Setting Up the Examples

We will use two simple tables throughout this guide:

-- employees table
EMPLOYEE_ID | LAST_NAME  | DEPARTMENT_ID
------------|------------|-------------
101         | King       | 10
102         | Kochhar    | 20
103         | De Haan    | 20
104         | Hunold     | 60
105         | Ernst      | NULL  (no department assigned)

-- departments table
DEPARTMENT_ID | DEPARTMENT_NAME
--------------|----------------
10            | Administration
20            | Marketing
30            | Purchasing    (no employees)
60            | IT

INNER JOIN (or just JOIN)

Returns only rows where there is a match in both tables. Non-matching rows from either table are excluded.

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

-- Results:
-- King    | Administration
-- Kochhar | Marketing
-- De Haan | Marketing
-- Hunold  | IT

-- Note: Ernst (NULL department) and Purchasing (no employees) are both excluded

INNER JOIN is the most common JOIN type. Use it when you only want rows that have matches in both tables.

LEFT OUTER JOIN (LEFT JOIN)

Returns all rows from the left table, plus matching rows from the right table. Where there is no match in the right table, NULL values are returned for the right table columns.

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

-- Results:
-- King    | Administration
-- Kochhar | Marketing
-- De Haan | Marketing
-- Hunold  | IT
-- Ernst   | NULL           ← Ernst included even with no department match

-- "Purchasing" still not shown (it's a right-table-only row)

Use LEFT JOIN when you want all rows from the left table, even those without a match. Classic example: show all employees, including those not assigned to any department.

RIGHT OUTER JOIN (RIGHT JOIN)

The mirror of LEFT JOIN. Returns all rows from the right table, plus matching rows from the left table. Non-matching left-table rows show NULL.

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

-- Results:
-- King    | Administration
-- Kochhar | Marketing
-- De Haan | Marketing
-- Hunold  | IT
-- NULL    | Purchasing    ← Purchasing shown even with no employees

-- Ernst not shown (LEFT table-only row)

In practice, RIGHT JOIN is rarely used because you can achieve the same result by swapping the table order and using LEFT JOIN. It is better practice to write your JOINs left-to-right in reading order.

FULL OUTER JOIN

Returns all rows from both tables. Where there is no match, NULL fills in for the missing side.

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

-- Results:
-- King    | Administration
-- Kochhar | Marketing
-- De Haan | Marketing
-- Hunold  | IT
-- Ernst   | NULL           ← No department
-- NULL    | Purchasing     ← No employees

-- Every row from both tables appears

Use FULL OUTER JOIN for data reconciliation — comparing two datasets and identifying rows that exist in one but not the other.

CROSS JOIN

Returns the Cartesian product — every possible combination of rows from both tables. If employees has 5 rows and departments has 4 rows, CROSS JOIN returns 20 rows.

SELECT e.last_name, d.department_name
FROM   employees e
CROSS JOIN departments d;

-- Returns all 5 × 4 = 20 combinations
-- Every employee paired with every department

CROSS JOIN has niche uses: generating test data, creating all combinations for a scheduling grid, or pairing all products with all price tiers. Beware of accidentally creating CROSS JOINs by forgetting a JOIN condition — it is a common performance catastrophe.

SELF JOIN

A JOIN of a table to itself. Used when a table has a hierarchical or self-referential relationship — like an employees table where each employee has a MANAGER_ID that references another employee’s EMPLOYEE_ID:

SELECT 
  emp.last_name  AS employee,
  mgr.last_name  AS manager
FROM   employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.employee_id
ORDER BY emp.last_name;

-- Results show each employee alongside their manager's name
-- Top-level employees (no manager) show NULL in the manager column

Oracle’s Traditional (+) Outer Join Syntax

Before SQL:1999 standard JOIN syntax, Oracle used a proprietary (+) notation for outer joins. You will encounter this in legacy code:

-- Oracle traditional syntax (still works, but avoid in new code)
SELECT e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id(+);
-- The (+) on the right means: LEFT outer join
-- The table without (+) is the "all rows" table

-- Equivalent modern syntax:
SELECT e.last_name, d.department_name
FROM   employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

The (+) syntax is Oracle-specific and not portable. Use ANSI JOIN syntax for all new code.

Joining Multiple Tables

-- Three-table join: Orders + Customers + Products
SELECT 
  o.order_id,
  c.customer_name,
  p.product_name,
  ol.quantity,
  ol.unit_price,
  ol.quantity * ol.unit_price AS line_total
FROM   orders o
JOIN   customers c  ON o.customer_id  = c.customer_id
JOIN   order_lines ol ON o.order_id   = ol.order_id
JOIN   products p   ON ol.product_id  = p.product_id
WHERE  o.order_date >= TRUNC(SYSDATE, 'MM')  -- Current month orders
ORDER BY o.order_id, p.product_name;

Each JOIN adds one more table to the result. Oracle’s optimizer determines the most efficient join order internally.

Non-Equi Joins

Most JOINs use equality (=) in the condition. Non-equi joins use other operators — useful for range-based lookups:

-- Assign salary grade based on a salary range table
SELECT 
  e.last_name,
  e.salary,
  sg.grade_level
FROM   employees e
JOIN   salary_grades sg ON e.salary BETWEEN sg.min_salary AND sg.max_salary
ORDER BY e.salary DESC;

JOIN Performance Tips

• Index JOIN columns: The columns used in ON clauses should be indexed on both sides
• Join on the same data type: Implicit type conversions prevent index use
• Filter early: Apply WHERE conditions on the individual tables before they are joined — this reduces the number of rows being joined
• Avoid SELECT * in joins: Retrieve only the columns you need, especially when joining wide tables
• Check execution plans: Look for NESTED LOOPS, HASH JOIN, or SORT-MERGE JOIN operations and verify they make sense for your data volumes

Quick Reference

JOIN Type	Returns	Use When
INNER JOIN	Matching rows only	You only want records with data in both tables
LEFT JOIN	All left + matching right	You want all left-table rows, even without a match
RIGHT JOIN	All right + matching left	Same as LEFT but swap table order instead
FULL OUTER JOIN	All rows from both tables	Data reconciliation, finding orphaned records
CROSS JOIN	Every combination	Generating combinations (use carefully)
SELF JOIN	Table joined to itself	Hierarchical/self-referential data

Conclusion

JOINs are fundamental — there is almost no meaningful SQL query that does not use at least one. INNER JOIN handles the majority of everyday queries. LEFT JOIN handles the common case of “all records from one side, even without a match.” FULL OUTER JOIN and CROSS JOIN have specific, powerful use cases.

Once JOINs become second nature, you will find that most complex query problems reduce to figuring out which tables to join and what type of JOIN to use. That clarity makes SQL writing much faster and more reliable.