Combining tables like VLOOKUP: JOIN orders with customers and products

The VLOOKUP that broke on Monday. Picture Monday morning at the online shop. Your manager wants last week's orders with the customer's name and city next to each one. The orders sheet only has a customer ID, so you write a VLOOKUP into the customers sheet and drag it down twenty thousand rows. It works, until someone inserts a column in the customers sheet and every lookup quietly points at the wrong data. That's the fragile part of spreadsheets: the link between tables lives in a formula you have to rebuild every time. In SQL, that link is one short line in your query, and it keeps working however many rows or columns the tables grow.

What you will learn. By the end of this lesson you'll be able to do three things. First, link the orders table to the customers table with JOIN, and understand exactly what the ON part does. Second, chain a second join so every order shows the customer's name and the product's name and price, all in one query. Third, use LEFT JOIN to keep rows that have no match, which is SQL's answer to the dreaded hash N A error you get from VLOOKUP. We'll work with the same online shop database from the last two lessons, so the tables will feel familiar.

Two tables, one shared key. Why are names not stored in the orders table in the first place? Because the shop would repeat Ana's name and city on every order she ever places, and a typo on one row would make her look like two people. So the database stores each customer once, with an id, and each order just carries that id in a column called customer id. A column that points to a row in another table is called a key. Think of it as the lookup value in VLOOKUP: the thing both sheets have in common. Every join you write starts with one question: which column in this table matches which column in that one?

Your first JOIN. Here are two small tables: three customers and four orders. Now we type our query. We start FROM orders, because we want one row per order. Then JOIN customers says we're bringing in the second table, and ON says how rows pair up: the customer's id must equal the order's customer id column. Watch each order get matched on the customer id. Order one hundred and one has customer one, so it pairs with Ana. Order one hundred and two has customer three, so it pairs with Chen. And here's the finished result: every order now carries a real name, and we never dragged a single formula.

What ON really does. Let's slow down on the ON line, because it's the heart of every join. ON c dot id equals o dot customer id reads as: pair an order with a customer whenever these two numbers are equal. The letter before the dot tells SQL which table the column comes from, so c dot id is the id in customers and o dot customer id is the customer id column in orders. In VLOOKUP terms, o dot customer id is your lookup value and c dot id is the first column of your lookup range. You can write the two sides in either order; SQL treats equals the same both ways. The SQLite documentation page shown here lists the full JOIN syntax if you want the details.

Short names with aliases. You may have noticed the single letters after the table names. Writing orders o gives the orders table a short nickname, called an alias, so you type o dot amount instead of orders dot amount every time. Aliases matter more once two tables share a column name. Both orders and customers have a column called id, and if you just write id, SQLite stops with an error saying the column name is ambiguous, meaning it can't tell which one you mean. So get into the habit now: every column in a join query gets its table letter in front, even when it's unique. Your future self reading the query will thank you.

Three tables in one query. Real orders point to two other tables: who bought, and what they bought. So from here on, the orders table also records which product was bought and how many, in a product id column and a quantity column. In Excel that's two separate VLOOKUP columns. In SQL you just add a second JOIN line, and it can use columns from both lookups right away. In our small sample, a mug costs eight fifty and a lamp costs thirty. In the query on screen, the first join brings in the customer, and the second join brings in the product through the product id. Because both are joined, we can multiply quantity by price in the same SELECT. The result rows under the query show order one hundred and one as Ana buying a lamp for thirty, and order one hundred and two as Ben buying four mugs, which comes to thirty four.

LEFT JOIN keeps the unmatched rows. A plain JOIN only keeps rows that find a partner, so a customer who never ordered simply vanishes. That's a problem if your manager asks how many orders each customer placed, including the ones who placed none. Here are four customers now, and Dina has never bought anything. Let's write the left join, starting from customers. LEFT means keep every row from the first table, the one named in FROM. Watch as Dina finds no match, yet she stays in, with empty order columns called NULL, which is SQL's version of hash N A. Grouping gives one row per customer, and because COUNT skips NULLs, her count comes out as zero.

Join first, then summarize. Now combine this with the last lesson. What if the manager wants total revenue per city, but city lives in customers and price lives in products? You join first, then group. SQL builds the wide joined table, one row per order with name, city, product and price, and only then does GROUP BY collapse it. The query on screen does exactly that: two joins, then a sum of quantity times price for each city. It's exactly the pivot table you'd build after adding lookup columns, except the lookups and the pivot live in one query you can rerun next month. That's the shape of our project in the next lesson.

Common mistake: the wrong ON. The most common join mistake doesn't produce an error; it produces believable wrong numbers. If you type ON c dot id equals o dot id, SQL happily pairs order number one with customer number one, which means nothing at all. And if the ON condition is missing or too loose, each order can pair with many customers, so your totals double or triple. SQLite won't warn you in either case. A simple habit protects you: count the orders table on its own, then count the joined result. When you join orders to customers, the row count should stay the same. If it grows or shrinks, check your ON line before you trust any sum.

Recap. Let's recap. First, JOIN brings a second table into your query, and ON is the matching rule: it pairs the lookup value with the first column of the range, just like VLOOKUP, but written once instead of dragged down thousands of rows. Second, you can chain joins, so one query shows each order with its customer and its product, and you can calculate across all of them or group them like a pivot table. Third, a plain JOIN drops rows without a partner, while LEFT JOIN keeps every row from the first table and fills the gaps with NULL. And always compare row counts before and after a join, so a wrong ON line never slips into your report.

Your exercise. Here's your task before the next lesson. The shop's buyer wants to know which products nobody is buying. Write a query that starts from the products table, left joins the orders table on the product id, and counts the orders for each product name, so a product with no orders shows a count of zero. Then sort the result so those zero rows appear at the top. Remember to count a column from orders, not a star, so the empty rows really count as zero. Run it in SQLite, then check that the number of result rows equals the number of products. Next time, we'll put everything together into the monthly sales report.