SQL Joins for Analysts

By the end of this lesson you will be able to combine tables with joins, choose between INNER and LEFT JOIN for a business question, find things that are missing (customers who never ordered, products that never sold) and avoid the double-counting trap that catches many new analysts.

Company data is split across many tables, just as a school keeps students in one register, fees in another and marks in a third. To answer "did the students who paid late score lower?", the office must match the registers by roll number. A join does that matching in SQL, using a shared column called a key.

INNER JOIN: only the matches

An INNER JOIN keeps rows that have a match in both tables. Business question: list each order with the customer's name and city.

SELECT o.order_id,
       o.order_date,
       c.first_name,
       c.city,
       o.status
FROM orders AS o
INNER JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_id
LIMIT 6;

order_id | order_date | first_name | city     | status
---------+------------+------------+----------+----------
101      | 2025-01-05 | Aarav      | Rohtak   | delivered
102      | 2025-01-12 | Priya      | Jaipur   | delivered
103      | 2025-01-20 | Rohan      | Mumbai   | delivered
104      | 2025-02-02 | Aarav      | Rohtak   | delivered
105      | 2025-02-14 | Ananya     | Chennai  | cancelled
106      | 2025-02-28 | Kabir      | Ludhiana | delivered
(6 rows)

What you should see: six orders, each with a customer name. The ON clause says which columns must match. The short names o and c are aliases; they keep long queries readable. Writing just JOIN means the same as INNER JOIN.

LEFT JOIN: keep everything on the left

A LEFT JOIN keeps every row from the first (left) table, even when there is no match. Where there is no match, the right table's columns are NULL. Business question: how many orders has each customer placed, including customers with none?

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

first_name | city      | orders
-----------+-----------+-------
Aarav      | Rohtak    | 3
Priya      | Jaipur    | 2
Rohan      | Mumbai    | 2
Ananya     | Chennai   | 1
Arjun      | Hyderabad | 1
Farhan     | Lucknow   | 1
Isha       | Gurugram  | 1
Kabir      | Ludhiana  | 1
Meera      | Kochi     | 1
Sneha      | Delhi     | 1
Vikram     | Rohtak    | 1
Divya      | Pune      | 0
(12 rows)

What you should see: all 12 customers, including Divya with 0 orders. With an INNER JOIN, she would vanish silently, and your "average orders per customer" would be too high. Notice COUNT(o.order_id) rather than COUNT(*). For a customer with no orders, the LEFT JOIN still produces one row with NULLs, and COUNT(*) would count it as 1.

Finding what is missing

Some of the most valuable questions are about absence. Customers who never bought anything could be targeted by marketing. Products that never sold might be badly priced or hidden on the website. The pattern is a LEFT JOIN plus WHERE right_table.key IS NULL. This is called an anti-join.

SELECT p.product_name,
       p.category,
       p.price,
       p.stock
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
WHERE oi.item_id IS NULL;

product_name    | category    | price | stock
----------------+-------------+-------+------
Graphics Tablet | Accessories | 4599  | 12
(1 row)

What you should see: one product, the Graphics Tablet, that appears in no order at all. It has 12 in stock, so availability is not the reason. That is a question for the product team: is it priced too high, or hard to find on the website?

The double-counting trap

Here is a mistake that has embarrassed many analysts. Suppose each order had a delivery charge of ₹50 stored on the order. If you join orders to order items and then sum the delivery charge, every order with two items is counted twice.

Watch it happen. We create a delivery charge with 50 AS delivery_fee and count it before and after the join:

SELECT 'orders only' AS method,
       SUM(50) AS total_delivery_fees
FROM orders
UNION ALL
SELECT 'after joining items',
       SUM(50)
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id;

method              | total_delivery_fees
--------------------+--------------------
orders only         | 750
after joining items | 1100
(2 rows)

What you should see: the second total is higher, even though nothing changed in the business. The join multiplied the rows. This is called fan-out: one row on the "one" side fans out into many rows on the "many" side.

How to avoid it:

1. Before a join, write down what one row means in each table ("one order", "one item").
2. After the join, count rows and compare with what you expect.
3. Sum order-level values (like delivery fees) on the orders table before joining, or aggregate the items per order first.

The CTEs lesson shows a clean way to aggregate first and join afterwards.

Joining more than two tables

Real questions often need three or four tables. Join one at a time, each with its own ON. Business question: which cities buy the most Accessories, by revenue?

SELECT c.city,
       SUM(oi.quantity * oi.unit_price) AS accessories_revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN products AS p ON p.product_id = oi.product_id
WHERE p.category = 'Accessories'
  AND o.status <> 'cancelled'
GROUP BY c.city
ORDER BY accessories_revenue DESC;

city      | accessories_revenue
----------+--------------------
Rohtak    | 7195
Mumbai    | 5398
Lucknow   | 3798
Ludhiana  | 2598
Hyderabad | 2199
Delhi     | 799
(6 rows)

What you should see: cities ranked by Accessories revenue. The path through the tables follows the keys: customers to orders, orders to items, items to products. Drawing that path on paper before writing a big join is a habit worth building.

Common mistakes

• Using INNER JOIN when you must keep everyone. Customers or products with no match disappear.
• COUNT(*) after a LEFT JOIN. It counts the NULL row as 1; count a column from the right table instead.
• Fan-out. Summing order-level values after joining to items multiplies them.
• Joining on the wrong column. The query runs, the numbers are wrong. Check a few rows.

Key points

• A join matches rows from two tables using a key column.
• INNER JOIN keeps only matches; LEFT JOIN keeps every row from the left table.
• LEFT JOIN plus IS NULL finds missing things, such as customers who never ordered.
• Joining a "one" table to a "many" table multiplies rows; aggregate before you join.
• Always check row counts and a few joined rows by eye.

SELECT c.first_name,
       c.city,
       c.joined_on
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.joined_on;