Lesson 7 – Displaying Data from Multiple Tables Using JOINs (or: persuading your tables to talk to each other) - SQL Guide

And look, normalized databases are great for design and terrible for reporting. All the interesting information is split across five different tables—employees here, departments there, jobs somewhere else—so a simple question like “who does what, where?” suddenly requires joins.

This lesson is about teaching your SELECT statements to navigate that mess.

You will learn to:

Write SELECT statements that join data from multiple tables.
Use equijoins and nonequijoins.
Join a table to itself using a self‑join.
Use INNER and OUTER joins (LEFT, RIGHT, FULL).
Recognize and (mostly) avoid Cartesian products via CROSS JOIN.

1. Why JOIN at All?

Because the database is normalized:

EMPLOYEES has employee_id, last_name, job_id, department_id, manager_id, etc.
JOBS has job_id, job_title, min/max salary.
DEPARTMENTS has department_id, department_name, location_id.
LOCATIONS has location_id, city, country_id.

To answer questions like “list all employees with their job titles and department names”, you must join tables on related columns, typically primary key ↔ foreign key pairs:

employees.job_id ↔ jobs.job_id
employees.department_id ↔ departments.department_id

Joins recombine normalized pieces into a single result set.

2. ANSI JOIN Types Overview

Oracle and MySQL both support ANSI join syntax, including:

INNER JOIN – only rows that satisfy the join condition.
LEFT [OUTER] JOIN – all rows from the left table, plus matches on the right.
RIGHT [OUTER] JOIN – all rows from the right table, plus matches on the left.
FULL [OUTER] JOIN (Oracle only) – all rows from both tables (matches and non‑matches).
CROSS JOIN – Cartesian product (every row with every row).
NATURAL JOIN – joins on all columns with the same name and type.
JOIN ... USING (column_list) – join on specific shared column names.
JOIN ... ON condition – explicitly specify the join condition.

The ON and USING forms are what you’ll rely on the most; NATURAL JOIN is convenient but can be dangerously magical.

3. INNER JOIN with ON – The Workhorse

Standard pattern:

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

Key points:

This is an INNER JOIN: rows are returned only where e.department_id = d.department_id.
If an employee’s department_id is NULL or doesn’t match any row in DEPARTMENTS, that employee is excluded.

You can include the INNER keyword explicitly:

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

Same result, just more explicit.

3.1 Table aliases and ambiguous columns

When both tables have a column with the same name (e.g., department_id), you must qualify it:

SELECT department_id
    FROM   employees, departments;
    -- ERROR: column ambiguously defined

Fix with table (or alias) prefixes:

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

Use meaningful aliases (e, emp, d, dept) so your future self can understand the join.

Note: in Oracle, you may not use AS for table aliases:

FROM employees AS e   -- invalid in Oracle
    FROM employees e      -- valid

4. USING and NATURAL JOIN – Shortcuts with Caveats

4.1 JOIN ... USING

If both tables have a column with the same name and compatible type, you can use USING:

SELECT last_name,
           department_name,
           department_id
    FROM   employees  e
    JOIN   departments d
           USING (department_id);

Rules:

USING (department_id) is shorthand for ON e.department_id = d.department_id.
When selecting department_id in this case:
Do not qualify it with a table alias: department_id is fine.
e.department_id or d.department_id in the SELECT will cause:
ORA-25154: column part of USING clause cannot have qualifier.

4.2 NATURAL JOIN

NATURAL JOIN automatically joins on all columns that:

Have the same name in both tables, and
Have compatible data types.

Example:

SELECT last_name,
           department_name
    FROM   employees
    NATURAL JOIN departments;

Behind the scenes, Oracle looks for all identically named columns (e.g., department_id, manager_id) and joins on all of them. So if both tables share department_id and manager_id, you’re effectively doing:

... JOIN ... USING (department_id, manager_id)

This can dramatically reduce the number of rows returned compared to a join on just one column.

Caution: NATURAL JOIN is convenient but fragile:

If someone later adds another identically named column, your join behavior changes.
If columns share a name but not type, you get an error.

Use it sparingly and only when you truly control the schema.

5. Joining More Than Two Tables

You can keep adding joins as long as the relationships make sense.

Example – employees, departments, and locations:

SELECT e.last_name,
           d.department_name,
           l.city
    FROM   employees  e
    JOIN   departments d
           ON e.department_id = d.department_id
    JOIN   locations  l
           ON d.location_id = l.location_id;

Here:

First join employees ↔ departments by department_id.
Then join departments ↔ locations by location_id.

You can keep going for as many tables as your query (and your brain) can handle.

Conditions unrelated to the joins can go either in the ON clauses or in a trailing WHERE:

SELECT e.last_name,
           d.department_name
    FROM   employees  e
    JOIN   departments d
           ON e.department_id = d.department_id
    WHERE  e.manager_id = 149;

or:

... JOIN departments d
         ON e.department_id = d.department_id
        AND e.manager_id = 149;

Both are valid; the key is to keep the join condition clear and separate from filter conditions.

6. Self-Joins – When a Table Is Its Own Boss

Sometimes the relationship you care about is entirely inside one table. Classic example: employees and their managers.

EMPLOYEES.employee_id uniquely identifies an employee.
EMPLOYEES.manager_id references another employee’s employee_id.

To pair each employee with their manager’s last name, you join the table to itself:

SELECT e.last_name AS emp,
           m.last_name AS mgr
    FROM   employees e
    JOIN   employees m
           ON e.manager_id = m.employee_id;

e = worker alias.
m = manager alias.
e.manager_id ↔ m.employee_id.

This is a self-join, and it’s how you turn a single table into a hierarchy (or at least an org chart).

7. Nonequijoins – When the Join Condition Is a Range

Not all relationships are equality-based. Sometimes you have ranges.

Example: JOB_GRADES table:

GRADE_LEVEL  LOWEST_SAL  HIGHEST_SAL
    -----------  ----------  ----------
    A            1000        2999
    B            3000        5999
    C            6000        9999
    D            10000       14999
    E            15000       24999

You want to assign each employee a grade based on their salary.

Nonequijoin:

SELECT e.last_name,
           e.salary,
           g.grade_level
    FROM   employees   e
    JOIN   job_grades  g
           ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

This is called a nonequijoin because the join condition uses BETWEEN (a range) instead of =.

8. OUTER JOINs – Bringing Back the Lonely Rows

INNER JOIN only returns rows that have a match on both sides. OUTER JOINs return matched rows plus the unmatched rows from one or both tables.

8.1 LEFT OUTER JOIN

All rows from the left table, and matching rows from the right; unmatched right‑side columns are NULL.

Example – all employees, even those without a department:

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

You get all 107 employees, including the one with no department_id.
For that employee, department_name is NULL.

8.2 RIGHT OUTER JOIN

All rows from the right table, and matching rows from the left.

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

You see every department, including those with no employees.
Employee columns are NULL where there’s no match.

8.3 FULL OUTER JOIN (Oracle only)

All rows from both tables:

Matched pairs.
Left‑only rows.
Right‑only rows.

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

This shows:

Employees with and without departments.
Departments with and without employees.

MySQL does not support FULL OUTER JOIN directly; you emulate it with UNION of left and right joins.

9. CROSS JOIN / Cartesian Product – The “Everything with Everything” Join

A CROSS JOIN (or an INNER JOIN without a condition) produces the Cartesian product:

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

If there are:

107 employees
28 departments

You get 107 × 28 = 2,996 rows.

The first 107 rows might show every employee “working” in department 10, the next 107 in department 20, and so on. This is rarely what you actually want, but it’s excellent at stress-testing your client tool.

Conceptually, a forgotten join condition with FROM employees e, departments d and a filterless WHERE clause can accidentally create the same cartesian product. Treat that as a smell.

10. MySQL Notes

All of the ANSI join patterns you’ve seen apply to MySQL too:

INNER JOIN, LEFT JOIN, RIGHT JOIN, CROSS JOIN.
JOIN ... USING(column_list) and JOIN ... ON condition.
NATURAL JOIN is supported but carries the same “magical join columns you didn’t ask for” risk.

MySQL does not support FULL OUTER JOIN directly; use LEFT JOIN ... UNION ... RIGHT JOIN minus the intersection if needed.

11. What You Should Now Be Able to Do

By the end of this lesson, you should be able to:

Write SELECT statements that join multiple tables using equijoins (ON / USING).
Explain and use NATURAL JOIN, USING, and ON appropriately.
Join a table to itself using a self‑join for hierarchical relationships.
Use nonequijoins for range‑based relationships like salary grades.
Use LEFT, RIGHT, and FULL OUTER JOINs to include unmatched rows.
Recognize and intentionally (or very cautiously) generate Cartesian products with CROSS JOIN.

You can now ask questions that span employees + jobs + departments + locations, which is exactly the point where people start calling your queries “the reporting layer”.