SQL Joins Explained: Types of Joins with Visual Examples

Your orders table stores customer_id, not the customer's name. Your customers table stores the name, not the orders. To see who placed each order, you need to connect the two tables. That connection is a JOIN. Almost every real analyst query you write will use at least one.

How a JOIN Works

A JOIN matches rows from two tables where a condition you define is true. The most common condition is a foreign key on one table equaling the primary key on another.

Here is the basic pattern:

SELECT
    c.first_name,
    c.last_name,
    o.order_id,
    o.total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

orders is the left table. customers is the right table. SQL compares every row in orders to every row in customers, keeps only the pairs where o.customer_id = c.customer_id, and returns those columns.

INNER JOIN: Only Matching Rows

JOIN and INNER JOIN mean the same thing. Both return only rows that have a match in both tables. If an order has no matching customer, it is excluded. If a customer has no orders, they are excluded too.

SELECT
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

Use this when you only care about rows that exist on both sides.

LEFT JOIN: Keep Every Row From the Left Table

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. Where there is no match, the right table columns come back as NULL.

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Customers who have never placed an order still appear in this result. Their order_id will be NULL. This is useful for finding customers with no activity.

Joining More Than Two Tables

You can chain multiple JOINs. Each one adds another table to the result. Start from the table at the center of your query and work outward.

SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    p.product_name,
    oi.quantity,
    oi.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;

This gives you a flat view of every item in every order, along with the customer who placed it. Each JOIN adds one table at a time.

Common Mistakes

-- wrong
SELECT order_id, first_name
FROM orders
JOIN customers;

Missing the ON clause causes a cross join, which pairs every row in one table with every row in the other. Always include ON.

-- correct
SELECT o.order_id, c.first_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- wrong
SELECT order_id, first_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

When a column name like order_id exists in only one table, this works. But as soon as the same column name appears in both tables, SQL throws an ambiguity error. Always prefix column names with the table alias.

-- correct
SELECT o.order_id, c.first_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- wrong
SELECT c.first_name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = o.customer_id;

Both sides of ON point to the same table. This is a typo that SQL will not catch. Always check that ON references one column from each table.

-- correct
SELECT c.first_name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

Quick Recap

• A JOIN connects rows from two tables using a shared column defined in the ON clause.
• INNER JOIN returns only rows with a match in both tables.
• LEFT JOIN returns all rows from the left table, with NULL for missing right-side data.
• You can chain multiple JOINs to bring in three or more tables at once.
• Always use table aliases and prefix every column name to avoid ambiguity errors.

SELECT
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id,
    o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

SELECT
    c.first_name,
    c.last_name,
    COUNT(o.order_id) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY order_count DESC;