SQL Joins for Data Analysis

Concept

Business data is split across related tables. customers.customer_id is a primary key: one row identifies one customer. orders.customer_id is a foreign key that points back to that customer. A join matches these keys so an analysis can use attributes and transactions together.

Why It Matters

Joins answer questions that one table cannot: revenue by customer region, customers with no orders, or product categories purchased by a segment. They also create model features. A careless join can silently multiply rows and inflate totals, so join logic is a data-quality decision.

INNER JOIN and LEFT JOIN

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

An INNER JOIN retains only matching customers and orders. A LEFT JOIN retains every customer, filling right-side fields with NULL when no order matches.

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

The expected result is customers who have never placed an order. This pattern is useful for activation analysis and outreach cohorts.

Intuition: Join Grain and Row Multiplication

One customer can place many orders. After joining customers to orders, one customer row appears once for each matching order; this row multiplication is expected for a one-to-many relationship. It becomes a problem only when the analysis expects one row per customer, or when duplicate keys make the multiplication unintentional. Aggregate orders to customer level before joining if the desired output is one row per customer.

Other Join Types

RIGHT JOIN keeps every row from the right table and is often rewritten as a left join with tables reversed for readability. FULL OUTER JOIN keeps matches plus unmatched rows from both sides; support varies by SQL dialect. Use the smallest join type that answers the question, and check the database documentation when syntax differs.

Best Practices

State the intended grain before writing SQL. Check primary-key uniqueness, duplicate keys, unmatched counts, and row counts before and after the join. Select key columns while validating. For revenue totals, compare against a trusted pre-join total to catch accidental multiplication.

Data Science Perspective

Joins combine source systems into analysis or feature tables: customer attributes plus order history, or products plus order items. They are essential for business analytics, but every downstream model inherits join mistakes.

Interview Perspective

Question: Why can a left join return more rows than its left table? Answer: a left-side row can match many right-side rows in a one-to-many relationship. Follow-up: how would you preserve one row per customer?

Practice Questions

1. Write a query for customers with no completed orders.
2. Explain why joining customers to order items can multiply customer rows.
3. Where should an order-status condition go if unmatched customers must remain?

Quick Quiz

1. What does an inner join keep? Answer: matched rows from both tables.
2. What does a foreign key do? Answer: relates a row to a key in another table.
3. Is row multiplication always an error? Answer: no; it is expected for valid one-to-many joins.

Validate Your Join

Before trusting a join, use this checklist:

1. Check whether the join keys are unique where you expect a primary key.
2. State the expected grain, such as one row per order or one row per customer.
3. Compare row counts and critical totals before and after joining.
4. Inspect unmatched rows and a few known IDs manually.
5. Confirm both keys use compatible types and formatting.

For example, one customer with three orders becomes three rows after customers joins to orders. That is not duplicate data; it is an order-grain result. It becomes an error only if you later sum a customer-level value three times, or if duplicate customer keys create matches you did not intend. Join on stable IDs, not names, and aggregate orders first when the final dataset must remain customer-grain.

Next Lesson

Next, organize multi-step analysis with subqueries, CTEs, and window functions.