Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 38 ยท Joins, Subqueries and Hierarchies

Joins in Oracle

Combine tables in Oracle with INNER, LEFT, RIGHT, FULL and CROSS joins, USING and ON, self joins, the old (+) outer join syntax and LATERAL joins.

Relational databases split data into tables so that each fact is stored once: an employee row stores a department_id, not the department's name and city. A join puts the pieces back together at query time. Joins are the skill that separates basic from useful SQL, and Oracle supports two syntaxes: the modern ANSI JOIN ... ON form, and the old comma-and-(+) form that fills decades of existing code.

All examples use the company database. The join keywords are identical in Oracle, so the runnable queries work unchanged there; only the old (+) operator needs a stand-in.

flowchart LR
  E["employees<br/>department_id"] -- "department_id = id" --> D["departments<br/>id, name, office"]
  E -- "manager_id = id" --> E2["employees<br/>(manager)"]

A join links rows through matching key values. A self join links a table to itself.

INNER JOIN

INNER JOIN returns only the rows that have a match on both sides. INNER is optional.

SQL Employees with their department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Notice the table aliases e and d. In Oracle you write employees e, with no AS (the keyword AS is not allowed before a table alias, though it is for a column alias). Prefix every column with its alias so the query is clear and no ORA-00918: column ambiguously defined can occur.

Employees whose department_id is NULL (the CEO and the contractor) are not in the result: NULL never equals anything.

LEFT, RIGHT and FULL OUTER JOIN

Outer joins keep unmatched rows and fill the missing side with NULL.

Join Keeps unmatched rows from
LEFT OUTER JOIN The left table
RIGHT OUTER JOIN The right table
FULL OUTER JOIN Both tables
SQL LEFT JOIN keeps everyone ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

This returns the two employees with no department. The pattern LEFT JOIN ... WHERE right.key IS NULL is an anti-join: "rows with no match". It is the same as NOT EXISTS.

SQL Headcount per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Use count(e.id), not count(*): for a department with no employees count(*) would return 1 (the department row itself), while count(e.id) returns 0 because e.id is NULL.

A condition on the optional side belongs in ON, not in WHERE. If you write WHERE e.salary > 90000, unmatched rows vanish and the outer join becomes an inner join:

SQL
-- departments with their well-paid employees; departments without any still appear
SELECT d.name, e.last_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id AND e.salary > 90000;

USING and NATURAL JOIN

When the join column has the same name on both sides, USING is shorter:

SQL
SELECT employee_id, last_name, department_name
FROM employees JOIN departments USING (department_id);

With USING, the shared column appears once and must not be qualified: department_id, not e.department_id. NATURAL JOIN goes further and joins on all same-named columns automatically, which is dangerous: adding a column called created_at to both tables silently changes the result. Avoid it.

CROSS JOIN and self joins

CROSS JOIN pairs every row with every row (a Cartesian product): 6 rows times 4 rows gives 24. It is useful for generating combinations, such as every product in every size:

SQL Cross join of two small sets

Press Run (or Ctrl + Enter) to see the result here.

A self join joins a table to itself using two different aliases. The classic use is an employee-manager relationship:

SQL Self join: employee and manager ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The LEFT JOIN keeps the CEO, who has no manager.

Joining more than two tables

Add one JOIN ... ON per extra table. Read it from left to right:

SQL Four tables ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The old Oracle syntax: comma joins and (+)

Before Oracle 9i there was no JOIN keyword. Tables went in the FROM list separated by commas and the join condition went in WHERE. For outer joins, Oracle added the (+) marker on the optional side:

SQL
-- inner join, old style
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- left outer join, old style: (+) goes on the side that may be missing
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id (+);

Forgetting the WHERE condition in the comma style silently produces a Cartesian product. The (+) operator also cannot express a FULL OUTER JOIN and has odd rules with OR and subqueries. Oracle recommends ANSI syntax for new code; you only need to be able to read the old form. The stand-in below is the same left join in ANSI style:

SQL Stand-in for the (+) left join ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

LATERAL and CROSS APPLY

Since 12c, a subquery in the FROM clause may refer to columns of tables on its left, using LATERAL or CROSS APPLY / OUTER APPLY:

SQL
SELECT d.name, top.last_name, top.salary
FROM departments d
CROSS APPLY (
  SELECT last_name, salary
  FROM employees e
  WHERE e.department_id = d.department_id
  ORDER BY salary DESC
  FETCH FIRST 2 ROWS ONLY
) top;

This is the clean way to get "top 2 per group" when you need a row-limited subquery per outer row. OUTER APPLY keeps outer rows with no inner rows, like a left join.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which join returns employees even if they have no department?

    A LEFT JOIN keeps every row from the left table (employees) and fills the right side with NULLs when nothing matches.
  2. 2. In an Oracle query, how do you alias a table named employees?

    Oracle does not allow AS before a table alias. Write the alias after the table name.
  3. 3. In the old syntax, where does the (+) go for a left outer join from employees to departments?

    (+) marks the optional side, the table that may have no matching row, which is departments here.
  4. 4. You write LEFT JOIN d ... WHERE d.country = 'DE'. What is the effect?

    A WHERE condition on the optional side filters out the NULL-filled rows. Put the condition in the ON clause to keep them.
  5. 5. What does a CROSS JOIN of a 6-row table and a 4-row table return?

    A cross join pairs every row with every row: 6 x 4 = 24.

Summary

  • A join recombines normalized tables through matching key values; prefer ANSI JOIN ... ON.
  • INNER keeps matches only; LEFT, RIGHT and FULL keep unmatched rows; conditions on the optional side go in ON.
  • Table aliases in Oracle take no AS; USING merges a shared column; avoid NATURAL JOIN.
  • A self join uses two aliases of one table; CROSS JOIN produces every combination.
  • The comma plus (+) syntax is legacy: know how to read it, and use LATERAL/CROSS APPLY for per-row subqueries.

Next: Subqueries and Set Operators