Joins

Difficulty: Intermediate · ~10 min read

Overview

A join combines rows from two or more tables based on a related column between them. Joins are the heart of relational SQL — they're how you assemble a row from data that lives in normalized tables.

DB2 supports the standard join families:

Join type	Returns
`INNER JOIN`	Only rows that match in both tables
`LEFT OUTER JOIN`	All rows from the left + matching from the right (NULL where missing)
`RIGHT OUTER JOIN`	All rows from the right + matching from the left
`FULL OUTER JOIN`	All rows from both, NULL where no match
`CROSS JOIN`	Cartesian product — every row × every row
Self join	A table joined to itself (employees → managers, etc.)

DB2 also supports the older comma-separated join syntax (FROM a, b WHERE a.id = b.a_id), but new code should always use explicit ANSI joins for clarity.

Syntax

SELECT cols
FROM   table_a a
[INNER | LEFT | RIGHT | FULL] [OUTER] JOIN table_b b
  ON   a.col = b.col
[WHERE ...]
[ORDER BY ...];

We'll use two tables throughout:

CREATE TABLE departments (
  dept_id   INTEGER PRIMARY KEY,
  dept_name VARCHAR(40) NOT NULL
);

CREATE TABLE employees (
  emp_id    INTEGER PRIMARY KEY,
  name      VARCHAR(80) NOT NULL,
  dept_id   INTEGER,             -- may be NULL (unassigned)
  manager_id INTEGER             -- self-reference to emp_id
);

INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR');
INSERT INTO employees VALUES
  (1, 'Alice',   10, NULL),
  (2, 'Bob',     10, 1),
  (3, 'Carla',   20, NULL),
  (4, 'David',   NULL, 3),
  (5, 'Eva',     40, NULL);  -- dept 40 doesn't exist

Examples

Example 1: INNER JOIN

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

Output:

NAME    DEPT_NAME
------  -----------
Alice   Engineering
Bob     Engineering
Carla   Sales

David is missing (no dept_id); Eva is missing (dept 40 doesn't exist).

Example 2: LEFT OUTER JOIN

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

Output:

NAME    DEPT_NAME
------  -----------
Alice   Engineering
Bob     Engineering
Carla   Sales
David   -
Eva     -

Every employee row is returned, even those with no matching department.

Example 3: FULL OUTER JOIN

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

Output:

NAME    DEPT_NAME
------  -----------
Alice   Engineering
Bob     Engineering
Carla   Sales
David   -
Eva     -
-       HR              <- HR has no employees

Example 4: Self join (employee → manager)

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

Output:

EMPLOYEE  MANAGER
--------  -------
Alice     -
Bob       Alice
Carla     -
David     Carla
Eva       -

The same table appears twice with two aliases. Self-joins solve hierarchical queries.

Example 5: Multi-way join

CREATE TABLE projects (
  proj_id   INTEGER PRIMARY KEY,
  proj_name VARCHAR(80),
  dept_id   INTEGER REFERENCES departments(dept_id)
);

INSERT INTO projects VALUES (100, 'Apollo', 10), (101, 'Mercury', 10);

SELECT e.name, d.dept_name, p.proj_name
FROM   employees e
JOIN   departments d ON e.dept_id = d.dept_id
JOIN   projects    p ON p.dept_id = d.dept_id;

Rows multiply across the join — Engineering has 2 employees × 2 projects = 4 rows.

Example 6: CROSS JOIN (cartesian product)

SELECT d.dept_name, p.proj_name
FROM   departments d
CROSS JOIN projects p;

Rarely useful directly, but handy for generating combinations (calendars, test data).

Example 7: Filtering vs. joining (a classic gotcha)

LEFT JOIN ... WHERE and LEFT JOIN ... AND behave differently!

-- Departments + only engineers, but keep all departments
SELECT d.dept_name, e.name
FROM   departments d
LEFT JOIN employees e
  ON   e.dept_id = d.dept_id
  AND  e.name LIKE 'A%';            -- filter on join

-- Vs. accidentally turning the LEFT join into an INNER
SELECT d.dept_name, e.name
FROM   departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE  e.name LIKE 'A%';            -- nulls eliminated here

The first version keeps every department; the second drops departments with no A% employee because the WHERE runs after the join and discards NULL employee names.

Notes & Tips

Always use ANSI JOIN ... ON syntax in new code. The legacy comma form makes outer joins awkward and is harder to read.
DB2's optimizer is smart about join order — you usually don't need to hand-tune it. But for very large tables, the order of conditions in ON can affect plan choice on rare occasions.
For LEFT JOIN followed by a WHERE filter on the right table's columns, expect the join to behave as an INNER — move the filter into the ON if you want to preserve null-padding.
DB2 supports JOIN ... USING (col) as a shorthand when the column has the same name in both tables.
NATURAL JOIN (auto-match on same-named columns) exists but is dangerous — schema changes can silently change query behaviour. Avoid it in production code.

Practice Exercises

Find every department, with a column showing the number of employees in it (use LEFT JOIN + GROUP BY so empty departments show 0).
List employees whose dept_id doesn't exist in departments (use a LEFT JOIN + WHERE dept_id IS NULL on the right side).
Write a self-join to find pairs of employees in the same department, without duplicate pairs (hint: WHERE e1.emp_id < e2.emp_id).
Use FULL OUTER JOIN to list every department and every employee, showing which sides have orphans.
Generate every (department, project) combination using a CROSS JOIN.

Quick Quiz

Q1. What's the difference between INNER JOIN and LEFT JOIN?

Show answer

INNER JOIN returns only rows where the join condition matches in both tables. LEFT JOIN returns every row from the left table plus matching rows from the right — when there's no match, the right-side columns are NULL. Use LEFT JOIN when "missing on the right side" is information you care about (e.g. departments with no employees).

Q2. Why does LEFT JOIN ... WHERE right_col = X often behave like an INNER JOIN?

Show answer

Because the WHERE clause runs after the join produces the rows. For unmatched rows the right side is NULL, and NULL = X is unknown (filtered out), so those rows disappear. Move the filter into the ON clause (ON ... AND right_col = X) if you want to preserve the unmatched left rows.

Q3. What is a self join useful for?

Show answer

Querying hierarchical or paired data within a single table. Classic example: an employees table where each row has a manager_id pointing to another row's emp_id. A self join with two aliases (e and m) lets you return the employee name and their manager's name in one row.

Next Up

Joins let us combine rows — next we'll combine values within a row using DB2's built-in functions.