Connecting Tables: JOINs and More Essential SQL

Why Data Gets Split Across Multiple Tables

Storing every piece of related information in a single table quickly leads to repeated data. Consider storing each customer's order history directly inside the customer table: every new order would require repeating that customer's name and email again, wasting space and creating a risk that the same customer's name is spelled differently across different rows.

Problematic single-table design:
| customer_name | email          | order_id | order_date |
|----------------|----------------|----------|------------|
| Alice Smith    | [email protected]   | 101      | 2026-01-05 |
| Alice Smith    | [email protected]   | 102      | 2026-01-12 |

Alice's name and email are duplicated for every order

Splitting this into two related tables — one for customers, one for orders — stores each customer's details exactly once, and each order simply references which customer placed it.

Foreign Keys: The Glue Connecting Tables

A Foreign Key is a column in one table that references the Primary Key, discussed earlier in this series, of another table, establishing a link between a row in one table and a row in another.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This foreign key constraint does more than just document the relationship; the database actively enforces it, refusing to insert an order that references a customer_id that does not actually exist in the customers table — a safeguard called Referential Integrity that prevents orphaned or inconsistent data.

Retrieving Data Across Tables: The JOIN Clause

Once data is split across tables, retrieving a complete picture — such as a customer's name alongside their order dates — requires combining rows from both tables using a JOIN.

INNER JOIN: Only Matching Rows

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

An INNER JOIN returns only rows where a match exists in both tables — a customer with no orders would not appear at all in this result, since there is no matching row in the orders table to pair it with.

LEFT JOIN: Keep Every Row from One Side

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

A LEFT JOIN returns every row from the left table (customers) regardless of whether a match exists in the right table (orders), filling in NULL for the order columns when no matching order exists. This is the right choice whenever the goal is to see every customer, including those who have never placed an order.

Choosing Between JOIN Types

INNER JOIN: only rows with a match on both sides
LEFT JOIN:  all rows from the left table, matched
            data from the right where available
RIGHT JOIN: the mirror image of LEFT JOIN
FULL JOIN:  all rows from both tables, matched
            where possible, NULL where not

Combining JOIN with Aggregation

JOINs are frequently combined with the GROUP BY and aggregate functions discussed earlier in this series to answer questions spanning multiple tables.

SELECT customers.name, COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name;

This query counts how many orders each customer has placed, correctly showing a count of zero for customers who have never ordered, thanks to the LEFT JOIN preserving their row even without a match.

Revisiting Table and Data Management

Beyond the basic CREATE TABLE covered earlier in this series, real database work often requires modifying a table's structure after it already contains data, using ALTER TABLE.

-- Add a new column to an existing table
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

-- Remove a column no longer needed
ALTER TABLE customers DROP COLUMN phone;

When deleting data that other tables depend on through foreign keys, the database's referential integrity enforcement, discussed earlier in this article, will block the deletion by default unless the dependent rows are removed first, or unless the foreign key is explicitly configured with a cascading behavior that defines what should happen to related rows automatically.

Why Mastering JOINs Is a Turning Point

Understanding how to properly split data across related tables and reliably reconnect it using JOINs is the single most important skill separating basic SQL usage from genuine relational database competency. Nearly every real-world database contains dozens of interconnected tables, and the ability to correctly combine data from several of them at once, without accidentally duplicating or losing rows, is essential for writing correct queries and, more importantly, for designing well-structured databases in the first place — the deeper design principles covered throughout the rest of this series.