SQL Joins: INNER, LEFT and How They Work

Your orders table doesn't store a customer's name. It stores customer_id. So when someone asks "who bought the Monitor?" you can't answer from one table. The name lives in customers, the order lives in orders, and the two only connect through that id. Joins are how you stitch them back together in a single query.

Why the data is split up in the first place

Look at the three tables you've been querying all series. A customer's name and city live once in customers. A product's name and price live once in products. An order doesn't repeat any of that. It just points:

-- orders columns
id | customer_id | product_id | quantity | order_date
1  | 1           | 1          | 1        | 2026-03-02

That customer_id of 1 means "customer with id 1," and product_id of 1 means "product with id 1." The order references the other two tables by their id instead of copying their data. This is on purpose. If you stored the customer's name inside every order and Maya changed her city, you'd have to hunt down and edit every order she ever placed. Store it once, reference it everywhere, and there's a single source of truth.

The cost of that tidiness is that a single row rarely tells the whole story. The order says customer_id is 1. Great, but who's customer 1? To get a readable answer you have to follow the pointer. That following-the-pointer is exactly what a join does.

INNER JOIN: follow the key

An INNER JOIN takes rows from two tables and pairs up the ones whose keys match. Here's every order matched to the customer who placed it:

Read the ON clause as the rule for what "matches": pair an order with a customer whenever orders.customer_id equals customers.id. SQLite walks the orders table, and for each order it finds the customer with the matching id and glues the two rows side by side. Now every result row has the order and the human name. No more bare ids.

customer_id here is the join key: the column whose value in one table points at a row in another. Picking the right key is the whole game. Join orders to customers on a key that doesn't relate them and you get garbage rows. Join on customer_id = id and every pairing is meaningful.

Table aliases keep it readable

Writing orders. and customers. in front of every column gets old fast, and once you join three tables it's unbearable. Give each table a short alias right after its name and use that instead:

orders AS o means "from here on, orders is o." Same query as before, far less noise. The AS is optional (FROM orders o works identically), but spelling it out reads clearly. Most SQL you'll meet in the wild uses single-letter aliases like this: o for orders, c for customers, p for products.

Selecting columns from more than one table

Once tables are joined, your SELECT list can pull columns from any of them, in any order. You're building a custom result shape out of pieces from each side. Mix and match what the question actually needs:

The WHERE filters the joined result, so this reads as "orders placed by Mumbai customers, showing the customer's name and city alongside each order." Everything you already know (WHERE, ORDER BY, GROUP BY) works on a join exactly the way it works on a single table. The join just hands those clauses a wider set of columns to work with.

LEFT JOIN: keep everything on the left, even with no match

Sometimes the rows without a match are the interesting ones. "Which customers have never ordered anything?" is a real question, and an INNER JOIN can't answer it, because those customers vanish. A LEFT JOIN keeps every row from the left table (the one in FROM) whether or not it finds a partner on the right.

Scroll the result. Every customer shows up, but Rohan's order_id is NULL. That NULL is the whole point: it means "this customer exists, but there was no matching order to pair with." SQL fills the right-side columns with NULL when the left row finds nobody. INNER JOIN would have hidden Rohan entirely. LEFT JOIN keeps him and flags the gap.

Which makes finding the never-ordered customers a one-liner. Keep only the rows where the match failed:

WHERE o.id IS NULL keeps only customers whose LEFT JOIN found no order. Rohan is your answer. This "LEFT JOIN, then filter for NULL" pattern is one you'll reach for constantly: rows in A that have no counterpart in B.

Joining three tables: who bought what

Two tables answered "who placed this order." To answer "who bought what," you need the product name too, and that lives in a third table. No problem, chain another join on. Each JOIN ... ON adds one more table, matched on its own key:

Now each row reads like a sentence: Maya bought 1 Keyboard at 49.99. The orders table is the hub in the middle. It holds both customer_id and product_id, so it's what lets you reach the customer on one side and the product on the other. Aliasing the two name columns (c.name AS customer, p.name AS product) keeps the output unambiguous, since both tables have a name.

Here's the shape of those relationships. Every order points back at one customer and one product:

The orders table sits between the other two, holding a foreign key to each. That hub-and-spoke shape is everywhere in real databases: a central table of events or transactions, each row pointing out at the things it involves.

A word on RIGHT and FULL joins

You'll also see RIGHT JOIN (keep every row from the right table) and FULL JOIN (keep unmatched rows from both sides). Modern SQLite supports both (older versions didn't), but in practice you'll rarely need them. A RIGHT JOIN is just a LEFT JOIN with the tables swapped, and FULL joins are uncommon outside reporting. INNER and LEFT are the two workhorses. Learn those cold and you've covered the overwhelming majority of joins you'll ever write. The exact grammar lives in the SQLite join-operator reference if you want the full picture.

Recap and what's next

A join follows a key from one table to another so you can read related data in a single result. INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table and fills the right with NULL when there's no match, which is how you find the gaps, like customers who never ordered. Aliases (o, c, p) keep the SQL readable, your SELECT can pull columns from any joined table, and chaining a second JOIN lets you reach across three tables at once to answer "who bought what."

So far each query has pulled rows directly. Next we nest one query inside another: subqueries, where the result of one SELECT feeds into another and you can ask questions like "which customers spent more than the average?" If you need a refresher on summarizing rows first, double back to GROUP BY and HAVING.