Data & SQL
SQL joins explained for business analysts
Part of our guide to business analyst skills.
Why joins exist
Databases split information into several tables on purpose. A shop does not repeat a customer's name on every order line; it stores the customer once, and each order simply carries the customer's ID. This keeps data tidy and avoids contradictions (one customer cannot have two different spellings of the same name). The cost is that an answer like "which customer placed the biggest order" needs data from two tables at once.
A join is how you ask the database to stitch tables together. You name the two tables and tell it which columns must match. That matching pair is usually a primary key in one table (like customers.customer_id) and a foreign key in the other (like orders.customer_id). If those terms are new, the data model and entity relationship diagram entries explain how tables relate.
If you are brand new to queries, start with SQL for business analysts and come back. Everything below is read-only SELECT work.
Why a business analyst needs to understand joins
Joins are where most wrong numbers are born. A report that double-counts revenue, a customer list that is missing people, a migration check that says "all good" when it is not: these often trace back to the wrong join type or a join on the wrong column. A business analyst who understands joins can:
- Read a developer's query and say whether it matches the requirement.
- Explain why two reports disagree ("one counts customers with orders, the other counts all customers").
- Write acceptance criteria about missing or duplicate data that testers can actually check.
- Spot "orphan" records, such as orders pointing to a customer who no longer exists, during data validation.
The sample data: CartNest
CartNest is a fictional online store. Three tiny tables are enough to show every behaviour. Notice the deliberate traps: Olga and Pavel have never ordered, and order 9004 points to customer 99, who does not exist.
customers
| customer_id | name |
|---|---|
| 1 | Mia |
| 2 | Noah |
| 3 | Olga |
| 4 | Pavel |
orders
| order_id | customer_id | order_total |
|---|---|---|
| 9001 | 1 | 40 |
| 9002 | 1 | 25 |
| 9003 | 2 | 60 |
| 9004 | 99 | 15 |
order_items
| item_id | order_id | sku |
|---|---|---|
| 1 | 9001 | MUG |
| 2 | 9001 | CAP |
| 3 | 9001 | PEN |
| 4 | 9002 | MUG |
| 5 | 9003 | CAP |
One customer can have many orders, and one order can have many items. This "one to many" shape is the cause of the biggest join trap, which we will reach in a moment.
INNER JOIN: only the matches
An inner join returns a row only when both tables have a match. Think of it as "show me customers who have orders, and their orders".
SELECT c.name, o.order_id, o.order_total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
| name | order_id | order_total |
|---|---|---|
| Mia | 9001 | 40 |
| Mia | 9002 | 25 |
| Noah | 9003 | 60 |
Three rows. Olga and Pavel vanished because they have no orders, and order 9004 vanished because customer 99 does not exist. That silent disappearance is the point and the danger. If a requirement says "list all customers", an inner join is the wrong tool. Writing just JOIN means INNER JOIN in most databases.
LEFT JOIN: keep everyone on the left
A left join keeps every row from the first (left) table, and adds matching rows from the second. Where there is no match, the columns from the second table are empty (NULL, the database word for "no value").
SELECT c.name, o.order_id, o.order_total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
| name | order_id | order_total |
|---|---|---|
| Mia | 9001 | 40 |
| Mia | 9002 | 25 |
| Noah | 9003 | 60 |
| Olga | (empty) | (empty) |
| Pavel | (empty) | (empty) |
Now all four customers appear. A very useful trick is the anti-join: add WHERE o.order_id IS NULL to keep only the left rows with no match.
SELECT c.name
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.name;
Result: Olga and Pavel. This answers "which customers have never ordered", a classic marketing and retention question. Flip the tables and you find the orphans: orders with no customer.
SELECT o.order_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
Result: order 9004 with customer_id 99. That one row is a data-quality defect you can raise as a bug report with evidence.
RIGHT and FULL joins
A RIGHT JOIN is a left join with the tables swapped, so most analysts just reorder the tables and use LEFT JOIN. A FULL OUTER JOIN keeps everything from both sides: matches, unmatched customers and unmatched orders. It is handy for reconciliation, such as comparing two systems and listing what is in one but not the other.
SELECT c.name, o.order_id, o.order_total
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;
| name | order_id | order_total |
|---|---|---|
| (empty) | 9004 | 15 |
| Mia | 9001 | 40 |
| Mia | 9002 | 25 |
| Noah | 9003 | 60 |
| Olga | (empty) | (empty) |
| Pavel | (empty) | (empty) |
Six rows: the orphan order, the three matches and the two customers without orders. Support for RIGHT and FULL varies by database and version; recent SQLite versions support them, while some older ones and some products do not. When in doubt, check the documentation, such as the SQLite language reference, or use two left joins combined.
A quick memory aid: inner is the overlap, left is the overlap plus the left-only rows, full is the overlap plus both sides' leftovers.
Diagram in words
- INNER JOIN: Mia’s two orders and Noah’s one. Olga, Pavel and orphan order 9004 vanish.
- LEFT JOIN: the three matches plus Olga and Pavel with empty order columns.
- RIGHT JOIN: the three matches plus order 9004 with an empty name. Most analysts swap the tables and use LEFT JOIN instead.
- FULL JOIN: matches, Olga and Pavel, and the orphan order 9004. Useful for reconciliation.
The big trap: row multiplication (fan-out)
Because one order has many items, joining orders to order_items repeats the order once per item. If you then add up the order totals, you count each order several times. Finance asks: "What is the total value of orders that have items?"
SELECT SUM(o.order_total) AS wrong_revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id;
Result: 205. But orders 9001, 9002 and 9003 are worth 40 + 25 + 60 = 125. Order 9001 has three items, so its 40 was counted three times (3 x 40 + 25 + 60 = 205). The correct approach is to avoid joining at the wrong level of detail:
SELECT SUM(order_total) AS right_revenue
FROM orders
WHERE order_id IN (SELECT order_id FROM order_items);
Result: 125. For the whole company, SELECT SUM(order_total) FROM orders; returns 140 (it includes the orphan order 9004). Three different numbers (205, 125, 140) from three reasonable-looking queries is exactly why you must define the question carefully. Counting items per order is a legitimate use of the join, because there we want one row per item:
SELECT o.order_id, COUNT(i.item_id) AS item_count
FROM orders AS o
LEFT JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;
Result: 9001 has 3, 9002 has 1, 9003 has 1, 9004 has 0. Notice COUNT(i.item_id) rather than COUNT(*): counting a column from the right table gives 0 for order 9004, while COUNT(*) would say 1.
Diagram in words
- Before the join: three orders with items, worth 40, 25 and 60.
- After the join: order 9001 appears three times (MUG, CAP, PEN), each row still carrying 40.
- Wrong sum after the join: 205. Correct value: 125. Whole company including orphan order 9004: 140.
ON versus WHERE in a LEFT JOIN
Where you put a filter changes the result. Ana wants every customer, and the order number only if the order is over 30.
SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id AND o.order_total > 30
ORDER BY c.customer_id;
Result: Mia 9001, Noah 9003, Olga (empty), Pavel (empty). All four customers remain. Move the same condition into WHERE o.order_total > 30 and Olga and Pavel disappear, because their order_total is empty and an empty value is never greater than 30. The LEFT JOIN has quietly turned into an inner join. Rule of thumb: filters on the right-hand table belong in ON if you want to keep unmatched left rows.
A fuller summary per customer, using COALESCE to show zero instead of empty:
SELECT c.name,
COUNT(o.order_id) AS orders,
COALESCE(SUM(o.order_total), 0) AS spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;
Result: Mia 2 orders and 65, Noah 1 and 60, Olga 0 and 0, Pavel 0 and 0.
Condition in ON
ON o.customer_id = c.customer_id AND o.order_total > 30
- All four customers remain
- Mia 9001, Noah 9003
- Olga and Pavel with empty orders
Condition in WHERE
WHERE o.order_total > 30
- Olga and Pavel disappear
- An empty total is never greater than 30
- The LEFT JOIN has quietly become an inner join
Step by step: choosing the right join
- Say the question in words. "All customers, with their orders if any" means a LEFT JOIN from customers. "Only customers who ordered" means INNER.
- Decide the grain: what should one result row represent? One customer, one order or one item? Joining to a table with more rows per key changes the grain.
- Pick the matching columns from the data dictionary. Never match on names or free-text.
- Count before and after. If 4 customers become 7 rows, you have fan-out; that may be fine, but you must know it.
- Test with the awkward rows: someone with no orders, an orphan order, a duplicate.
- Document the choice in the requirement or ticket so reviewers know it was deliberate.
Common join mistakes
- ✅ Use LEFT JOIN when the requirement says "all". ⚠️ Using INNER JOIN and silently dropping customers with no activity.
- ✅ Check row counts before and after a join. ⚠️ Summing a value from the "one" side after joining to the "many" side.
- ✅ Put right-table filters in ON for a left join. ⚠️ Filtering them in WHERE and accidentally turning it into an inner join.
- ✅ Join on keys (IDs). ⚠️ Joining on name or email, which can be duplicated or spelled differently.
- ✅ Use COUNT(column) to count matches. ⚠️ COUNT(*) after a left join counts the empty row as 1.
- ✅ Look for orphans and unmatched rows on purpose. ⚠️ Assuming every foreign key points to a real record.
- ✅ Write what the numbers mean. ⚠️ Handing over 205 with no explanation when the answer is 125.
Reader exercise
Using the CartNest tables, write these queries and check against the hints.
- List every order with the customer's name, including orders whose customer does not exist. (Hint: left join from orders; order 9004 shows an empty name.)
- Find customers who have ordered something over 50. (Hint: only Noah.)
- Count distinct customers who have at least one item in an order. (Hint: 2, Mia and Noah. Think about why
COUNT(DISTINCT c.customer_id)is safer thanCOUNT(*)here.)
Write one sentence for each saying which join you picked and why.
Practise joins in the BA Lab
In the BA Lab Data & SQL stage you work with a fictional company's tables and a browser SQL console, and the checks compare your result with the expected one and explain mismatches. It is practice, not a production database. More drills are on the free SQL practice page, and pricing shows which packages include the SQL stage. For the next step, read KPI vs metric, because the numbers you build with joins usually end up on a dashboard.
Frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table and fills the right-side columns with empty values when there is no match.
Which join should a business analyst use most often?
LEFT JOIN is used a lot for "show all of X and their Y if any" questions and for finding missing records. INNER JOIN is right when you only want matched rows.
Why does my total get bigger after I join a table?
Joining to a table that has several rows per key repeats the left-hand row for each match, so summing a value from the left table counts it multiple times. Aggregate at the right level or use a subquery.
What does NULL mean in a join result?
NULL means no value. In a left join it appears in right-hand columns when no matching row exists. Test it with IS NULL, never with = NULL.
Do all databases support FULL OUTER JOIN?
No. Support differs by product and version. If it is missing, combine a LEFT JOIN and a RIGHT JOIN (or swapped LEFT JOIN) with UNION, or check your database documentation.
This article is educational. All companies, people and numbers in the examples are fictional. BA Mentorship does not issue professional certifications and cannot guarantee any job or interview outcome.