SQL Joins: Combining Tables

Two Spreadsheets, One Answer

Imagine you run an online bookstore. You have two spreadsheets: one lists every customer, the other lists every order. A customer is in the first sheet. Their orders are in the second. Neither sheet alone can answer the question: "Which customers placed an order in the last 30 days?"

To answer it, you need to combine the sheets — match each order to the customer who placed it, then filter by date. In SQL, that combining operation is called a JOIN.

Joins are where the relational model pays off. Data stored separately, in normalized tables, gets connected at query time to answer any question the business needs.

Setup: The Tables We'll Join

customers

orders

Notice: Customer 3 (Clara) and 4 (Dan) have no orders. Order 104 has customer_id = 5, which doesn't exist in the customers table (orphaned row). These edge cases reveal the differences between join types.

INNER JOIN: Only Matching Rows

Returns rows where the join condition is satisfied in both tables. Non-matching rows are excluded entirely.

customers     orders
    ○             ○
    ○  ←match→   ○   ← INNER JOIN returns these
    ○             ○
    ○  (no match)     ← excluded
              ○  (no match)  ← excluded

SELECT customers.name, orders.product, orders.amount
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

Result:

Clara and Dan have no orders — they're excluded. Order 104's customer_id doesn't exist — it's excluded. Only rows with a match in both tables appear.

When to use: When you only want records that have a corresponding match. Most common join type.

LEFT JOIN (LEFT OUTER JOIN): All Left Rows

Returns all rows from the left table plus matching rows from the right table. Where no match exists, right-side columns are NULL.

customers     orders
    ○  ←match→   ○   ← included with order data
    ○  ←match→   ○   ← included with order data
    ○  (no match)    ← included with NULL order data
    ○  (no match)    ← included with NULL order data
              ○  (no match)  ← excluded (right-only)

SELECT customers.name, orders.product, orders.amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Result:

Clara and Dan appear with NULL for order columns — they exist in customers but have no orders. Order 104 still excluded (it's on the right side with no match).

When to use: "Show me all X, and if they have a Y, show that too." Most useful join after INNER JOIN.

Finding rows with no match (anti-join pattern):

-- Customers who have NEVER placed an order
SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

Result:

RIGHT JOIN (RIGHT OUTER JOIN): All Right Rows

The mirror image of LEFT JOIN. Returns all rows from the right table, plus matching rows from the left.

SELECT customers.name, orders.product, orders.amount
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Result:

Order 104 (Eraser) appears with NULL for customer name — it's in orders but has no matching customer.

In practice, RIGHT JOIN is rarely used. You can always rewrite it as a LEFT JOIN by swapping the table order. Most developers stick with LEFT JOIN for consistency.

FULL OUTER JOIN: All Rows from Both Tables

Returns all rows from both tables. Where no match exists on either side, NULLs fill the gaps. Not supported natively in MySQL — emulate with UNION.

-- PostgreSQL / SQL Server
SELECT customers.name, orders.product
FROM customers
FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;

-- MySQL equivalent
SELECT customers.name, orders.product
FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id
UNION
SELECT customers.name, orders.product
FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;

Result:

When to use: Auditing data mismatches between two tables. Finding orphans on both sides.

CROSS JOIN: Every Combination

Returns the Cartesian product — every row from the left paired with every row from the right. No ON condition.

SELECT customers.name, orders.product
FROM customers
CROSS JOIN orders;

4 customers × 4 orders = 16 rows. Every possible pairing.

When to use: Generating test data, creating a grid of all possible combinations (e.g., all products for all stores). Almost never used with production data due to explosive row counts.

SELF JOIN: A Table Joined to Itself

Used when rows in a table relate to other rows in the same table. Classic example: an employee hierarchy where each employee has a manager_id that references another employee's employee_id.

employees

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

Result:

The table is aliased twice (e for employees, m for managers) so SQL can distinguish which copy is which.

Join Types Reference

Common Mistake: Forgetting the ON Condition

-- This is accidentally a CROSS JOIN in older SQL
SELECT * FROM customers, orders;

-- This is intentionally a CROSS JOIN in modern SQL
SELECT * FROM customers CROSS JOIN orders;

-- This is the INNER JOIN you probably wanted
SELECT * FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

Without ON, every join between two tables becomes a Cartesian product. With 1,000 customers and 50,000 orders, that's 50 million rows returned.

Joining Three or More Tables

Joins chain naturally:

SELECT
    members.first_name,
    books.title,
    loans.loan_date,
    loans.due_date
FROM loans
INNER JOIN members ON loans.member_id = members.member_id
INNER JOIN books   ON loans.book_id   = books.book_id
WHERE loans.return_date IS NULL
ORDER BY loans.due_date;

This reads: "Show me the name of every member and the title of every book they currently have on loan, ordered by due date." Three tables, two joins, one readable query.

Next lesson: Advanced queries — aggregation, GROUP BY, subqueries, CTEs, and window functions.