SQL join

A SQL join combines rows from two tables where a column matches, like orders with their customers. The type of join decides what happens to rows without a match.

Select any words here to ask your Maestro about them.

Your customers table has 120 customers in four regions; 30 have never ordered, and the rest average 2.5 orders each. You write customers INNER JOIN orders and COUNT(*) by region. What total do you get?

A store with 120 customers in four regions; 30 have never ordered. Example data.

What it is: a JOIN combines rows from two tables where a column matches, like orders.customer_id with customers.customer_id. It's how SQL answers questions that live in two tables at once, much like XLOOKUP in a spreadsheet.

The types: INNER, LEFT, RIGHT and FULL OUTER decide what happens to rows that don't find a match. LEFT JOIN is the workhorse of analysis.

Where it breaks: a join can quietly change how many rows you have. Before and after every join, count the rows and ask what the grain of the result is.

Practice joins in the course SQL for Data People.

How it's used

Four joins, one question

customers and orders

INNER JOIN: only customers with at least one orderRows with no match in the other table disappear.
marked: LEFT JOIN: every customer; NULL where there's no orderThe most common in analysis: the left table rules.
RIGHT JOIN: every order, even one whose customer is missing
FULL OUTER JOIN: everything from both, matched where possible

Pick the join by asking which table's rows must all survive.

When to look closer

A filter after a LEFT JOIN

customers_and_orders.sql

SELECT c.customer_id, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id

You LEFT JOIN orders to keep every customer, then add WHERE o.status = 'paid'. What happens to customers with no orders?

Ask your Maestro about SQL join

It answers from the free course page closest to this word, the way it answers while you study.

Example

You: What does "SQL join" mean, in plain words?

Your Maestro: I'll explain it with a case of yours: it sticks better than a definition. Which two tables at work do you need to combine most often?

Asking about: Answers from the page "Can you clean data without losing rows? Check yourself and pick what's next"

Only this browser keeps what you ask here. How it works

Learn more

Join (SQL) (Wikipedia)

SQL
SQL is the language for asking a database for data: which columns, from which table, which rows and how to summarize them. You get a table back.
Database
A database stores data in connected tables, with rules for each column, so many people and programs can read and change it without stepping on each other.
Grain (of a table)
What one row of a table stands for: a person, a transaction, a planet, a person per language. Counting rows counts what you want only if you know the grain.
Star schema
A star schema arranges data for analysis: a fact table of events, like sales, in the center, joined by keys to dimension tables (customer, product, date) that describe them.

The whole glossary