Combining Tables — Connecting the Dots

Up to now, every query has pulled from a single table. That's fine for learning — but real data almost never lives in one place. Customers are in one table, their orders in another, and the payments in a third. The moment you need to connect them, you need a JOIN.

This is the post that intimidates people, but it shouldn't. A join is just a way of saying "match these two tables on a shared column." Like puzzle pieces clicking together.

Why data gets split up in the first place

Imagine storing everything in one giant table. Every order would repeat the customer's name, phone number, and address over and over. When Achieng changes her phone number, you'd have to update fifty rows. One missed update and the data is wrong.

So databases split data into related tables. Soko Fresh keeps:

• a customers table — each customer, once.
• an orders table — each order, once.

And crucially, each order stores a customer_id that points back to the customer who placed it. That little shared column is the hook that joins hang on.

Meet the two tables

customers

orders

Notice something important: customer 5, Umoja Gifts, has no orders. They signed up but haven't bought anything yet. That detail matters in a moment.

INNER JOIN: only the matches

The question: show me each order, with the customer's name next to it.

SELECT customers.name, orders.amount, orders.status
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Plain English: "Select the customer's name, the order amount and status, from the orders table joined to the customers table, where the customer_id matches."

The ON clause is the heart of the join — it tells SQL which column to match between the two tables. Here it says: match rows where orders.customer_id equals customers.customer_id.

The result:

Look at what happened: SQL took each order, looked up the matching customer by customer_id, and pulled the customer's name into the same row. Akinyi's name appears twice because they have two orders.

And note what's missing: Umoja Gifts. INNER JOIN only keeps rows that have a match on both sides. Since Umoja Gifts has no orders, they're left out.

LEFT JOIN: keep everything on the left

Sometimes you want the unmatched rows. The question: show me every customer, even those with no orders — and list their orders if they have any.

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Plain English: "Select customer name and order amount, from customers left-joined to orders on customer_id."

LEFT JOIN keeps every row from the left table (the one after FROM), and fills in matches from the right table where they exist. Where there's no match, you get NULL — SQL's word for "nothing here."

There's Umoja Gifts, right at the bottom, with NULL in the amount column — "no orders yet." That's the difference: LEFT JOIN includes the customers with nothing.

Which JOIN should I use?

Here's the decision guide, kept deliberately short:

For most business questions, INNER JOIN and LEFT JOIN cover nearly everything:

• "Show me orders with customer names" → INNER JOIN (an order always has a customer).
• "Show me all customers and their orders, including customers with none" → LEFT JOIN.
• "Which customers have never ordered?" → LEFT JOIN + a filter for NULL.

That last one is a genuinely useful query. Here it is:

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

Plain English: "Show me customer names, left-joined to orders, where there is no matching order."

The result is just Umoja Gifts — your list of customers to follow up with. A LEFT JOIN plus an IS NULL filter finds "the ones with nothing," which is often exactly who you want to reach.

A bigger example: who are our top customers?

Let's combine everything from the whole series so far. Rank our customers by total order value, top spender first:

SELECT customers.name, SUM(orders.amount) AS total_spent
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.name
ORDER BY total_spent DESC;

Read it through, left to right:

1. SELECT the customer name and the sum of their order amounts.
2. FROM customers, INNER JOIN orders on the shared customer_id.
3. GROUP BY customer name, so the sum is per customer.
4. ORDER BY total spent, descending.

The result:

Simba Electronics is your biggest customer. That's a join, a GROUP BY, an aggregate, and a sort — working together to answer a question a real business asks every week. And you can now read every line of it.

The mental model that makes joins click

If joins still feel fuzzy, hold onto this image: a join walks down one table, and for each row, reaches into the other table to find the matching row(s) using the shared key.

• The shared key is what the ON clause names.
• INNER JOIN drops rows that find no partner.
• LEFT JOIN keeps the left row and writes NULL where no partner exists.

That's genuinely all there is to it. The vocabulary is intimidating ("inner," "left," "on"), but the mechanics are just "match these, and decide what to do with the unmatched ones."

Try it yourself

On sqlbolt.com, work through the "JOINs" lessons — they're short and click quickly. Then, using the customers and orders tables above:

1. Write a query listing every order with its customer name (INNER JOIN).
2. List every customer and their total order count, including customers with zero orders (LEFT JOIN + COUNT).
3. Find customers who have never ordered (LEFT JOIN + IS NULL).

Next up, we take a detour into the topic everyone asks about: how AI fits into all of this. Now that you can read a join and trust its result, you're ready to use ChatGPT as a co-pilot — and to catch it when it's wrong.