SQL JOINs — 800 Orders Dropped by Wrong INNER JOIN

Every real-world application stores data across multiple tables. An e-commerce site keeps customers in one table, orders in another, and products in a third. That's not sloppy design — it's intentional. Splitting data this way (called normalisation) eliminates duplication and keeps your database consistent. But the moment you need to answer a question like 'show me every customer and what they ordered last month,' you need a way to bring those tables back together. That's exactly what JOINs were built for, and they're one of the most-used features in SQL.

Before JOINs existed, developers would pull raw data from each table separately and stitch it together in application code. This was slow, bug-prone, and pushed enormous amounts of data across the network. JOINs moved that logic into the database engine itself — where it can be optimised, indexed, and executed orders of magnitude faster than anything you'd write by hand in Python or Java.

By the end of this article you'll know not just the syntax of each JOIN type, but — more importantly — you'll know which JOIN to reach for in a given situation, why the wrong JOIN silently returns misleading results, and how to spot JOIN-related performance traps before they bite you in production.

INNER JOIN: Only the Records That Match on Both Sides

An INNER JOIN returns rows only when a matching value exists in both tables. Think of it as the intersection in a Venn diagram. If a customer has never placed an order, they won't appear in the result. If an order somehow has no matching customer, it won't appear either. Only the overlap makes the cut.

This is the right JOIN when you genuinely only care about complete relationships. 'Show me every order alongside the customer who placed it' — that's INNER JOIN territory. You're not interested in orders without customers (data anomalies you'd fix separately) or customers who haven't ordered yet (a different business question entirely).

One subtlety worth knowing: INNER JOIN is the default JOIN in most SQL dialects. Writing JOIN without a keyword in front of it gives you an INNER JOIN. Being explicit with INNER JOIN makes your intent clearer to anyone reading the query later — including future-you at 11pm debugging a production incident.

-- SETUP: Two tables representing a small online bookstore
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    full_name     VARCHAR(100),
    email         VARCHAR(100)
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    book_title    VARCHAR(150),
    order_total   DECIMAL(8, 2)
);

INSERT INTO customers VALUES
    (1, 'Alice Nguyen',   'alice@example.com'),
    (2, 'Ben Okafor',     'ben@example.com'),
    (3, 'Clara Svensson', 'clara@example.com');  -- Clara has NO orders yet

INSERT INTO orders VALUES
    (101, 1, 'The Pragmatic Programmer', 39.99),
    (102, 1, 'Clean Code',               34.99),
    (103, 2, 'Designing Data-Intensive Applications', 49.99),
    (104, 9, 'Unknown Book', 9.99);               -- customer_id 9 does NOT exist

SELECT
    c.full_name,
    c.email,
    o.order_id,
    o.book_title,
    o.order_total
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.full_name, o.order_id;

LEFT JOIN: Keep Everything From the Left, Match What You Can From the Right

A LEFT JOIN returns every row from the left (first) table, and fills in matching data from the right table where it exists. Where there's no match on the right side, SQL fills those columns with NULL. The left table is never filtered — it always shows up in full.

This is the JOIN you reach for when the left table contains the 'source of truth' and the right table has optional enrichment data. Classic scenarios: 'show me all customers and their orders (including customers who haven't ordered yet),' or 'list all products and how many times each has been reviewed (including products with zero reviews).'

NULL in the right-side columns is actually meaningful signal here — it tells you 'this row exists but has no matching data on the right.' You can exploit that intentionally by filtering WHERE right_table.column IS NULL, which gives you only the rows with no match. That pattern is called an anti-join and it's surprisingly powerful for finding gaps: unreviewed products, uninvoiced contracts, customers who never completed onboarding.

-- All customers and their orders — including customers with no orders
SELECT
    c.customer_id,
    c.full_name,
    o.order_id,
    o.book_title,
    COALESCE(CAST(o.order_total AS VARCHAR), 'No orders yet') AS order_summary
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

-- Anti-join: find customers who have NEVER ordered
SELECT c.customer_id, c.full_name, c.email
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

RIGHT JOIN and FULL OUTER JOIN: Completing the Picture

A RIGHT JOIN is the mirror image of LEFT JOIN — every row from the right table is preserved, and unmatched rows from the left get NULLs. In practice, most developers avoid RIGHT JOIN entirely because you can always rewrite it as a LEFT JOIN by swapping the table order. The result is identical, but LEFT JOIN reads more naturally left-to-right.

FULL OUTER JOIN is the union of LEFT and RIGHT JOIN — every row from both tables appears in the result. Where a match exists, the columns are populated. Where there's no match on either side, you get NULLs for the missing half. This is rare in everyday queries, but it's invaluable for data reconciliation work: comparing two systems to find records that exist in one but not the other, like syncing a CRM against a billing platform.

Not all databases support FULL OUTER JOIN natively. MySQL doesn't — but you can emulate it cleanly with a UNION of a LEFT JOIN and a RIGHT JOIN.

-- Reconciling two payment systems after a migration
SELECT
    COALESCE(l.payment_ref, n.payment_ref) AS payment_ref,
    l.amount        AS legacy_amount,
    n.amount        AS new_amount,
    CASE
        WHEN l.payment_ref IS NULL THEN 'New system only'
        WHEN n.payment_ref IS NULL THEN 'Legacy only — was it migrated?'
        ELSE 'Matched'
    END AS reconciliation_status
FROM legacy_payments AS l
FULL OUTER JOIN new_payments AS n
    ON l.payment_ref = n.payment_ref
ORDER BY payment_ref;

-- MySQL workaround (no FULL OUTER JOIN support)
SELECT l.payment_ref, n.payment_ref
FROM legacy_payments l LEFT JOIN new_payments n ON l.payment_ref = n.payment_ref
UNION
SELECT l.payment_ref, n.payment_ref
FROM legacy_payments l RIGHT JOIN new_payments n ON l.payment_ref = n.payment_ref;

JOIN Performance: Why Your Query Slows Down at Scale

Understanding JOIN types is only half the battle. In production, a perfectly correct JOIN can bring a database to its knees if you haven't thought about how the engine executes it.

The most important thing you can do for JOIN performance is index your JOIN columns. When you write ON orders.customer_id = customers.customer_id, both columns should be indexed. The primary key side is usually indexed automatically. The foreign key side often isn't — and that's where most JOIN slowdowns originate. Without an index, the database performs a full table scan for every row on the other side.

The second trap is joining on expressions or functions: ON LOWER(a.email) = LOWER(b.email). This forces a full scan because the index can't be used against a computed result. Store data in a consistent format at write time, not query time.

Finally, watch your row explosion problem. JOINing a one-to-many relationship and then running SUM() on it is a classic mistake. If a customer has 10 orders and you join to a promotions table where they have 3 active promos, your SUM suddenly operates on 30 rows instead of 10.

-- WRONG: naive join causes row multiplication before aggregation
SELECT
    c.full_name,
    SUM(o.order_total) AS total_spent  -- DOUBLES because of 2 promo rows!
FROM customers c
INNER JOIN orders o   ON c.customer_id = o.customer_id
INNER JOIN promos p   ON c.customer_id = p.customer_id
WHERE c.customer_id = 1
GROUP BY c.full_name;

-- CORRECT: pre-aggregate in CTEs before joining
WITH aggregated_orders AS (
    SELECT customer_id, SUM(order_total) AS total_spent, COUNT(*) AS order_count
    FROM orders GROUP BY customer_id
),
aggregated_promos AS (
    SELECT customer_id, COUNT(*) AS active_promos
    FROM promos GROUP BY customer_id
)
SELECT c.full_name, ao.total_spent, ao.order_count, COALESCE(ap.active_promos, 0)
FROM customers c
INNER JOIN aggregated_orders ao ON c.customer_id = ao.customer_id
LEFT  JOIN aggregated_promos ap ON c.customer_id = ap.customer_id;

-- Always index your foreign key JOIN columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_promos_customer_id ON promos(customer_id);