Working with Multiple SQL Tables

Real databases rarely store everything in one table. A well-designed schema splits data across multiple related tables — customers in one, orders in another, products in a third. Working with that data means combining it intelligently.

SQL provides several mechanisms for this: JOINs, subqueries, UNION, and CTEs. Each has its place.

Understanding Table Relationships

Before writing multi-table queries, understand how the tables relate:

One-to-Many (most common):
  One customer → many orders
  One order    → many order_items
  One product  → many order_items

Many-to-Many:
  Many students → many courses (via enrollment table)
  Many products → many tags (via product_tags table)

One-to-One:
  One user → one user_profile

These relationships are implemented with foreign keys: a column in the child table that references the primary key of the parent.

Joining Two Tables

The most common operation — fetch columns from related tables by matching on a shared key:

-- Orders with customer name
SELECT
    o.order_id,
    c.name AS customer_name,
    c.email,
    o.amount,
    o.status,
    o.created_at
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

Joining Three or More Tables

Chain joins — each join adds another table:

-- Order details: customer + order + items + product
SELECT
    c.name AS customer,
    o.order_id,
    o.created_at AS order_date,
    p.name AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN customers c    ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p    ON oi.product_id = p.product_id
WHERE o.created_at >= '2025-01-01'
ORDER BY o.created_at, o.order_id, p.name;

Including Unmatched Rows with LEFT JOIN

Use LEFT JOIN when you need all rows from the left table, even if there’s no match in the right:

-- All customers, including those who have never ordered
SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC;

Anti-Join: Find Non-Matching Rows

A LEFT JOIN + IS NULL filter finds rows in the left table that have NO match in the right:

-- Products that have never been ordered
SELECT p.product_id, p.name, p.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

-- Customers who haven't ordered in the last 6 months
SELECT c.customer_id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
    AND o.created_at >= NOW() - INTERVAL '6 months'
WHERE o.order_id IS NULL;

Subqueries for Multi-Table Filtering

When you need to filter rows based on aggregated data from another table:

-- Customers whose lifetime spend exceeds the average
SELECT c.customer_id, c.name
FROM customers c
WHERE (
    SELECT SUM(amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) > (
    SELECT AVG(customer_total)
    FROM (
        SELECT customer_id, SUM(amount) AS customer_total
        FROM orders
        GROUP BY customer_id
    ) t
);

CTEs for Multi-Step Multi-Table Queries

CTEs make complex multi-table queries readable by naming each step:

WITH
-- Step 1: revenue per customer
customer_revenue AS (
    SELECT
        customer_id,
        SUM(amount) AS lifetime_value,
        COUNT(*) AS order_count,
        MAX(created_at) AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
-- Step 2: classify customers
customer_tiers AS (
    SELECT
        customer_id,
        lifetime_value,
        order_count,
        last_order_date,
        CASE
            WHEN lifetime_value >= 5000 THEN 'Platinum'
            WHEN lifetime_value >= 1000 THEN 'Gold'
            WHEN lifetime_value >= 200  THEN 'Silver'
            ELSE 'Bronze'
        END AS tier
    FROM customer_revenue
)
-- Step 3: output with customer details
SELECT
    c.customer_id,
    c.name,
    c.email,
    ct.lifetime_value,
    ct.order_count,
    ct.tier,
    ct.last_order_date
FROM customers c
JOIN customer_tiers ct ON c.customer_id = ct.customer_id
ORDER BY ct.lifetime_value DESC;

UNION and UNION ALL: Stack Results Vertically

UNION combines rows from multiple queries into one result set. Both queries must return the same number of columns with compatible types:

-- Combine customers and suppliers into a single contacts list
SELECT name, email, 'customer' AS contact_type FROM customers
UNION ALL
SELECT company_name, contact_email, 'supplier' AS contact_type FROM suppliers
ORDER BY name;

UNION removes duplicates (slower). UNION ALL keeps all rows (faster). Use UNION only when you specifically need deduplication.

Many-to-Many via a Junction Table

A products-to-tags relationship requires a junction table:

-- Tables: products, tags, product_tags(product_id, tag_id)

-- Products with their tags as a comma-separated list
SELECT
    p.product_id,
    p.name,
    STRING_AGG(t.name, ', ' ORDER BY t.name) AS tags
FROM products p
JOIN product_tags pt ON p.product_id = pt.product_id
JOIN tags t ON pt.tag_id = t.tag_id
GROUP BY p.product_id, p.name
ORDER BY p.name;

-- Products that have ALL of the specified tags (tag intersection)
SELECT p.product_id, p.name
FROM products p
JOIN product_tags pt ON p.product_id = pt.product_id
JOIN tags t ON pt.tag_id = t.tag_id
WHERE t.name IN ('wireless', 'bluetooth')
GROUP BY p.product_id, p.name
HAVING COUNT(DISTINCT t.name) = 2;  -- must have both tags

Comparing Data Across Tables

A practical reporting pattern — compare two data sets to find differences:

-- Find customers in the CRM but not in the email platform
SELECT crm.email, crm.name
FROM crm_contacts crm
LEFT JOIN email_platform ep ON crm.email = ep.email
WHERE ep.email IS NULL;

-- Find records updated since last sync
SELECT a.customer_id, a.email AS current_email, b.email AS synced_email
FROM customers a
JOIN customer_sync_log b ON a.customer_id = b.customer_id
WHERE a.email != b.email
   OR a.updated_at > b.synced_at;

Performance Tips for Multi-Table Queries

Index foreign key columns. Every FK that’s joined on should have an index:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

Filter early. Apply WHERE conditions before joining to large tables:

-- Better: filter orders first, then join
FROM (SELECT * FROM orders WHERE status = 'completed') o
JOIN customers c ON o.customer_id = c.customer_id

Use EXPLAIN ANALYZE. See which joins are slow and where indexes are missing before optimizing.