SQL JOINs Explained: INNER, LEFT, RIGHT and FULL with Real Examples

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
-- Run these CREATE + INSERT statements first to follow along

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,           -- foreign key linking back to customers
    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),  -- Alice ordered twice
    (103, 2, 'Designing Data-Intensive Applications', 49.99),
    (104, 9, 'Unknown Book', 9.99);               -- customer_id 9 does NOT exist

-- THE QUERY: Find every order alongside the customer who placed it
-- INNER JOIN means: only rows where customer_id matches in BOTH tables
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   -- this is the bridge between the two tables
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.

-- Using the same bookstore tables from above
-- QUESTION: Which customers have placed orders, and which haven't ordered at all?
-- We want ALL customers in the result — LEFT JOIN is the right tool

SELECT
    c.customer_id,
    c.full_name,
    o.order_id,
    o.book_title,
    o.order_total,
    -- COALESCE lets us replace NULL with a friendlier label in the output
    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;

-- BONUS: Anti-join pattern — find customers who have NEVER ordered
-- The trick is filtering WHERE the right side is NULL after a LEFT JOIN
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;   -- only rows where NO match was found on the right

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, matching how humans think about 'start with this table and attach that one.'

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, for instance, doesn't — but you can emulate it cleanly with a UNION of a LEFT JOIN and a RIGHT JOIN, as shown below.

-- SCENARIO: Reconciling two payment systems after a migration
-- legacy_payments = old system, new_payments = new system
-- Goal: find payments that exist in one system but not the other

CREATE TABLE legacy_payments (
    payment_ref   VARCHAR(20) PRIMARY KEY,
    amount        DECIMAL(10, 2),
    paid_at       DATE
);

CREATE TABLE new_payments (
    payment_ref   VARCHAR(20) PRIMARY KEY,
    amount        DECIMAL(10, 2),
    paid_at       DATE
);

INSERT INTO legacy_payments VALUES
    ('PAY-001', 250.00, '2024-01-10'),
    ('PAY-002', 175.50, '2024-01-11'),
    ('PAY-003', 340.00, '2024-01-12');  -- PAY-003 is in legacy only

INSERT INTO new_payments VALUES
    ('PAY-001', 250.00, '2024-01-10'),
    ('PAY-002', 175.50, '2024-01-11'),
    ('PAY-004', 99.00,  '2024-01-13'); -- PAY-004 is in new system only

-- FULL OUTER JOIN (PostgreSQL / SQL Server / standard SQL)
-- Shows ALL payments from BOTH tables, nulls where one side is missing
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 — needs investigation'
        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 native FULL OUTER JOIN support)
-- Combine LEFT JOIN + RIGHT JOIN with UNION to get the same result
SELECT l.payment_ref AS legacy_ref, n.payment_ref AS new_ref
FROM legacy_payments l LEFT JOIN new_payments n ON l.payment_ref = n.payment_ref
UNION
SELECT l.payment_ref AS legacy_ref, n.payment_ref AS new_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 orders.customer_id and customers.customer_id 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 come from. 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 (built on raw values) can't be used against a computed result. Store data in a consistent format (lowercase emails, for instance) at write time, not at 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. Aggregate before joining, or use subqueries/CTEs to pre-aggregate.

-- PROBLEM: Row explosion when aggregating across multiple one-to-many joins
-- A customer has 3 orders totalling $100. They also have 2 promo codes.
-- Naive JOIN produces 6 rows (3 orders x 2 promos) — SUM doubles!

CREATE TABLE promos (
    promo_id     INT PRIMARY KEY,
    customer_id  INT,
    discount_pct INT
);

INSERT INTO promos VALUES
    (1, 1, 10),  -- Alice has two promos
    (2, 1, 15);

-- WRONG: naive join causes row multiplication before aggregation
SELECT
    c.full_name,
    SUM(o.order_total) AS total_spent  -- this will be DOUBLE the real value!
FROM customers AS c
INNER JOIN orders AS o   ON c.customer_id = o.customer_id
INNER JOIN promos AS p   ON c.customer_id = p.customer_id
WHERE c.customer_id = 1
GROUP BY c.full_name;

-- CORRECT: pre-aggregate in a subquery (CTE) before joining
WITH aggregated_orders AS (
    -- Summarise orders FIRST, before any other join
    SELECT
        customer_id,
        SUM(order_total)  AS total_spent,
        COUNT(order_id)   AS order_count
    FROM orders
    GROUP BY customer_id
),
aggregated_promos AS (
    SELECT
        customer_id,
        COUNT(promo_id)   AS active_promos
    FROM promos
    GROUP BY customer_id
)
SELECT
    c.full_name,
    ao.total_spent,
    ao.order_count,
    COALESCE(ap.active_promos, 0) AS active_promos
FROM customers AS c
INNER JOIN aggregated_orders AS ao  ON c.customer_id = ao.customer_id
LEFT  JOIN aggregated_promos AS ap  ON c.customer_id = ap.customer_id
ORDER BY c.full_name;

-- Also: add indexes on your JOIN columns
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_promos_customer_id ON promos(customer_id);

Frequently Asked Questions