SQL JOINs Explained with Simple Examples

A SQL JOIN combines rows from two or more tables using a column they share, such as a customer ID. Use INNER JOIN when you only want rows that match in both tables, LEFT JOIN when you want every row from the first table even if there is no match, and FULL OUTER JOIN when you want everything from both sides. This guide walks through each type with two tiny tables so you can see exactly which rows come back.

Why databases split data into tables

Relational databases store each kind of thing in its own table: customers in one, orders in another, products in a third. A customer's name and city are stored once, and each order simply points to the customer with a customer_id. This avoids repeating the same information thousands of times and keeps it consistent when something changes.

The price of that design is that most business questions need data from more than one table. "Total sales by customer city" needs the city (in customers) and the amount (in orders). JOINs are how you bring them back together. If you are new to queries, start with our SQL for beginners guide on SELECT, WHERE and ORDER BY.

The two sample tables

customers

orders

Notice two "imperfections" that exist in real data too: Delta Foods (customer 4) has no orders, and order 105 points to customer 9, who doesn't exist in the customers table. These two rows are what make each JOIN type behave differently.

INNER JOIN: only the matches

An INNER JOIN returns rows where the join condition is true on both sides.

SELECT c.name, o.order_id, o.amount
FROM customers AS c
INNER JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY o.order_id;

Delta Foods disappears (no orders) and so does order 105 (no matching customer). Ana appears twice because she has two orders: a JOIN returns one row per matching pair. Writing just JOIN is the same as INNER JOIN.

The c and o are table aliases. They keep queries short and make it clear which table each column comes from, which matters when both tables have a column with the same name.

LEFT JOIN: keep everything on the left

A LEFT JOIN (also called LEFT OUTER JOIN) returns every row from the first table, plus matching data from the second. When there is no match, the columns from the second table are NULL.

SELECT c.name, o.order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

The result is the same four rows as before plus one more: Delta Foods, NULL, NULL. Order 105 still doesn't appear, because it isn't in the left table.

Finding rows with no match

Combining a LEFT JOIN with IS NULL is one of the most useful patterns in analytics. It answers questions like "which customers have never ordered?"

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Result: Delta Foods. Swap the tables and you can find orders whose customer is missing, a common data-quality check before building a dashboard.

RIGHT, FULL OUTER and CROSS JOIN

RIGHT JOIN is a mirror of LEFT JOIN; most analysts simply swap the table order and use LEFT JOIN so queries read consistently. FULL OUTER JOIN is great for reconciling two lists, such as orders in the sales system vs invoices in accounting. MySQL doesn't support FULL OUTER JOIN directly (you can emulate it with a LEFT JOIN and a RIGHT JOIN combined with UNION), and SQLite added RIGHT and FULL joins only in version 3.39. CROSS JOIN has no ON clause; it is useful for building grids such as every store × every month.

A real business query: sales by customer

JOINs become powerful when combined with aggregation. This query lists every customer with their order count and total sales, including those with zero orders:

SELECT
    c.name,
    COUNT(o.order_id)          AS orders,
    COALESCE(SUM(o.amount), 0) AS total_sales
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total_sales DESC;

Result: Brooks Market 1250.00, Coastal Deli 780.50, Ana Rivera 470.00, Delta Foods 0. Note that COUNT(o.order_id) counts only non-null values, so Delta Foods correctly shows 0 orders, while COUNT(*) would show 1. Learn more in our guide to GROUP BY and aggregate functions.

Watch out for duplicated rows

If the join column is not unique on at least one side, rows multiply. Joining orders to a promotions table where a customer has three promotions repeats each order three times, and your sales total triples. Before joining, ask "is this key unique in this table?" and compare row counts before and after the join.

Joining more than two tables

Real reports often chain several JOINs. Imagine two more tables: order_items (one row per product in each order, with order_id, product_id and quantity) and products (with product_id and category). To get units sold by customer city and product category, you join step by step, each JOIN connecting to a table already in the query:

SELECT
    c.city,
    p.category,
    SUM(oi.quantity) AS units
FROM orders AS o
INNER JOIN customers   AS c  ON o.customer_id = c.customer_id
INNER JOIN order_items AS oi ON o.order_id    = oi.order_id
INNER JOIN products    AS p  ON oi.product_id = p.product_id
GROUP BY c.city, p.category;

Read it as a path: orders lead to customers, orders lead to their items, and items lead to products. Sketching that path on paper before writing the query is a simple habit that prevents most join mistakes.

Next steps

Recreate the two tables in a free database like SQLite or PostgreSQL and run every query above, predicting the row count before you press run. Then practice the LEFT JOIN + IS NULL pattern on your own data. When you're ready, move on to CTEs and subqueries to organize multi-step joins, and to star schema modeling to see how the same relationships work inside Power BI.

Watch: video lessons

▶

▶

Frequently asked questions