Joins

A join combines rows from two or more tables into a single result set, based on a related column between them. Joins are the heart of relational databases — they are how you bring data from multiple tables together into meaningful output.

Why Do We Split Data Into Multiple Tables?

This idea — putting related data into separate tables — is called normalisation. Here is why it matters:

Imagine storing all employee and department information in one table:

Problems:

• "IT", "London", "Mike" are stored three times. If IT moves to Paris, you must update 3 rows — and if you miss one, the data becomes inconsistent.
• What if a department exists but has no employees yet? There is nowhere to store it.

The solution: split into two tables with a relationship:

employees:

departments:

Now IT's information is stored once. Joins let you reassemble this into the combined view whenever you need it.

Tables Used in This Chapter

We will use two tables throughout:

employees:

departments:

Note: David has no department (NULL), and Finance has no employees.

Join Types — Visual Overview

INNER JOIN — Only Matching Rows

An INNER JOIN returns only rows where the join condition is satisfied in both tables. If a row in the left table has no match in the right table (or vice versa), it is excluded entirely.

Think of the Venn diagram: only the overlapping intersection — rows that exist in both tables.

-- Returns only employees who have a matching department
SELECT e.name,
       d.dept_name
FROM   employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Result:

David is excluded because his dept_id is NULL (no match in departments). Finance is excluded because no employees have dept_id = 30.

How the join works step by step:

1. Take each row in employees
2. Find rows in departments where dept_id matches
3. If a match is found, combine the two rows into one output row
4. If no match is found, skip the employee row entirely

The word INNER is optional — JOIN and INNER JOIN are the same:

-- These are identical:
FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id;
FROM employees e JOIN departments d ON e.dept_id = d.dept_id;

LEFT JOIN — All Left Rows, Plus Matches

A LEFT JOIN (also written LEFT OUTER JOIN) returns all rows from the left table plus any matching rows from the right table. For left rows with no match in the right table, the right table columns appear as NULL.

-- Returns ALL employees, with department info where available
SELECT e.name,
       d.dept_name
FROM   employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

Result:

David appears with dept_name = NULL because he has no matching department. Finance still does not appear because no employee belongs to it — the left table (employees) drives the result.

When to use LEFT JOIN:

• When you want all records from the left table, regardless of whether they have a match
• Finding employees with or without departments
• Checking which records from the left table are "orphaned" (no match)

RIGHT JOIN — All Right Rows, Plus Matches

A RIGHT JOIN returns all rows from the right table plus any matching rows from the left table. Unmatched left table columns are NULL.

-- Returns ALL departments, with employee info where available
SELECT e.name,
       d.dept_name
FROM   employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Result:

Finance appears with name = NULL because no employees are in it. David does not appear because the right table (departments) drives the result, and David does not appear in departments.

-- These two queries return the same data (with columns in different order):
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id

FULL OUTER JOIN — All Rows From Both Tables

A FULL OUTER JOIN returns every row from both tables. Where there is a match, columns from both sides are populated. Where there is no match, the unmatched side's columns are NULL.

SELECT e.name,
       d.dept_name
FROM   employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Result:

Both "unmatched" records appear: David (employee without a department) and Finance (department without employees).

CROSS JOIN — Every Row With Every Row

A CROSS JOIN produces the Cartesian product — every row from the left table combined with every row from the right table. No ON condition is needed.

-- 4 employees × 3 departments = 12 rows
SELECT e.name,
       d.dept_name
FROM   employees e
CROSS JOIN departments d;

Result (12 rows):

When is CROSS JOIN useful?

• Generating a complete grid of all possible combinations (e.g., all products × all sizes × all colours)
• Creating test data (combine a set of first names × last names)
• Building a calendar (all dates × all shift types)

SELF JOIN — A Table Joined to Itself

A SELF JOIN joins a table to itself. The most classic use case is an employee-manager relationship where both employees and managers are stored in the same employees table.

employees table (with manager_id):

-- Show each employee alongside their manager's name
SELECT e.name        AS employee,
       m.name        AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Result:

The same table is referenced twice using different aliases: e for the employee, m for the manager. The aliases are mandatory for self-joins — without them the database cannot tell which copy of the table you mean.

Other self-join use cases:

• Finding all products that cost the same as another product
• Finding employees hired in the same month as another employee
• Hierarchical categories (category → subcategory in the same table)

Joining Three or More Tables

You can chain as many JOIN clauses as needed, each adding another table to the result:

-- orders → customers → products (3-way join)
SELECT o.order_id,
       c.customer_name,
       p.product_name,
       o.quantity,
       o.quantity * p.price  AS line_total
FROM   orders o
JOIN   customers c  ON o.customer_id = c.customer_id
JOIN   products  p  ON o.product_id  = p.product_id
WHERE  o.order_date >= '2024-01-01'
ORDER BY o.order_date DESC;

Each JOIN adds one more table to the mix. The database typically works left to right, though the query optimiser may reorder joins for efficiency.

-- 4-table join: employee → department → location → country
SELECT e.name             AS employee,
       d.dept_name        AS department,
       l.city             AS office_city,
       c.country_name     AS country
FROM   employees       e
JOIN   departments     d ON e.dept_id       = d.dept_id
JOIN   locations       l ON d.location_id   = l.location_id
JOIN   countries       c ON l.country_code  = c.country_code;

Table Aliases — Best Practice Always

Table aliases shorten long table names and make queries much more readable. They are mandatory in self-joins and should be used in every join.

-- Without aliases: verbose and hard to read
SELECT employees.name, departments.dept_name
FROM   employees
JOIN   departments ON employees.dept_id = departments.dept_id;

-- With aliases: clean and readable
SELECT e.name, d.dept_name
FROM   employees   e
JOIN   departments d ON e.dept_id = d.dept_id;

When two tables have a column with the same name, you must qualify it with the table alias:

-- Both tables have dept_id — must specify which one you mean
SELECT e.emp_id,
       e.dept_id,    -- employee's dept_id
       d.dept_id,    -- department's dept_id (same value after the join)
       d.dept_name
FROM   employees e
JOIN   departments d ON e.dept_id = d.dept_id;

ON vs USING vs NATURAL JOIN

There are three ways to specify the join condition:

ON — Explicit, Recommended

-- Most flexible: columns can have different names
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

USING — When Column Names Are Identical

-- Shorthand when both tables have the same column name
FROM employees e
JOIN departments d USING (dept_id);

-- This eliminates one copy of dept_id in the result
-- Note: you cannot use table aliases with USING columns (just dept_id, not e.dept_id)

NATURAL JOIN — Automatic Match on Same-Named Columns (Avoid)

-- Automatically joins on all columns with matching names
FROM employees
NATURAL JOIN departments;

Anti-Join — Finding Rows With No Match

An anti-join finds rows in one table that have no corresponding row in another table. This is one of the most practically useful join patterns.

Pattern: LEFT JOIN + WHERE right_column IS NULL

-- Employees who are NOT assigned to any department
SELECT e.name
FROM   employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE  d.dept_id IS NULL;
-- Result: David (his dept_id is NULL, so no match in departments)

-- Departments that have NO employees
SELECT d.dept_name
FROM   departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE  e.emp_id IS NULL;
-- Result: Finance (no employees have dept_id = 30)

Non-Equi Joins — Joining on Ranges

Not all joins use the = operator. You can join on <, >, BETWEEN, and other conditions:

-- Assign salary grades to employees based on salary ranges
-- salary_grades: grade_id, min_sal, max_sal
SELECT e.name,
       e.salary,
       g.grade_name
FROM   employees e
JOIN   salary_grades g ON e.salary BETWEEN g.min_sal AND g.max_sal;

-- Find all employees who earn more than at least one person in their own department
SELECT a.name AS higher_earner,
       b.name AS lower_earner,
       a.dept
FROM   employees a
JOIN   employees b ON a.dept = b.dept
                   AND a.salary > b.salary;

Common Join Mistakes

Mistake 1: Forgetting the ON Clause (Accidental Cartesian Product)

-- WRONG: missing ON creates a CROSS JOIN (Cartesian product)
SELECT e.name, d.dept_name
FROM   employees e, departments d;    -- old-style syntax, no ON condition

-- Or with modern syntax but no condition:
FROM   employees e
JOIN   departments d;    -- some databases error, others silently cross join

-- CORRECT: always specify the ON condition
FROM   employees e
JOIN   departments d ON e.dept_id = d.dept_id;

Mistake 2: Ambiguous Column Names

-- WRONG: dept_id exists in both tables — which one?
SELECT emp_id, dept_id, dept_name
FROM   employees
JOIN   departments ON employees.dept_id = departments.dept_id;
-- ERROR: column "dept_id" is ambiguous

-- CORRECT: qualify with table alias
SELECT e.emp_id, e.dept_id, d.dept_name
FROM   employees   e
JOIN   departments d ON e.dept_id = d.dept_id;

Mistake 3: Using INNER JOIN When You Need LEFT JOIN

-- If some employees have no department, INNER JOIN silently drops them
-- You get a "correct" result that is incomplete — hard to notice

-- Always ask: should rows with no match be included or excluded?
-- Excluded: use INNER JOIN
-- Included (with NULLs for unmatched columns): use LEFT JOIN

Mistake 4: Multiplying Rows with One-to-Many Joins

-- If orders has multiple rows per customer, this join multiplies rows
SELECT c.name, c.email, o.order_id, o.total
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id;
-- Correct: one output row per order (customer info repeated per order)
-- Problem: if you then SUM(total) without understanding this, you get inflated numbers

JOIN Best Practices

-- 1. Always use explicit JOIN syntax (not the old comma-separated FROM)
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id    -- correct

-- NOT: FROM employees e, departments d WHERE e.dept_id = d.dept_id  (old style)

-- 2. Always alias your tables, especially with 3+ tables
SELECT e.name, d.dept_name, l.city
FROM   employees e
JOIN   departments d ON e.dept_id   = d.dept_id
JOIN   locations   l ON d.location_id = l.location_id;

-- 3. Qualify every column with its table alias when joining
SELECT e.emp_id,    -- not just emp_id
       e.name,
       d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- 4. Index the join columns for performance
CREATE INDEX idx_emp_dept_id ON employees(dept_id);
CREATE INDEX idx_dept_dept_id ON departments(dept_id);

-- 5. Understand the cardinality (one-to-one, one-to-many) of each join
-- before summing or counting, to avoid inflated results

Common Errors

Interview Corner

-- INNER JOIN: only employees who belong to a department
SELECT e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- LEFT JOIN: all employees, even those with no department
SELECT e.last_name, d.department_name   -- NULL for employees with no dept
FROM   employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Accidental Cartesian: 107 employees × 27 departments = 2889 rows
SELECT e.last_name, d.department_name
FROM   employees e, departments d;   -- missing WHERE e.department_id = d.department_id

Related Topics

• Subqueries — use subqueries as derived tables or for semi-join / anti-join patterns
• Aggregate Functions — GROUP BY after joins to summarise combined data
• Window Functions — ranking and running totals across joined result sets
• Indexes — index join columns to avoid full scans on large tables
• Explain Plan — read join operations (NESTED LOOPS, HASH JOIN, MERGE JOIN) in execution plans