Most real databases store information across multiple tables — and that means a single-table SELECT will only take you so far. This lesson teaches you how to combine tables using JOIN, understand why rows match or get dropped, and write clean multi-table queries from scratch.

Imagine you're a data analyst at a mid-sized e-commerce company. Your manager asks you a simple question: "Which customers placed orders last month, and what did they buy?" You open your database and find the information scattered across two separate tables — one holding customer names and contact details, another holding order records with product IDs and amounts. Neither table alone answers the question. You need to combine them.
This is the situation that makes SQL JOINs one of the most important skills in data work. Almost every real database splits its information across multiple tables by design — customer data lives separately from order data, product information lives separately from inventory data. If you can only query one table at a time, you can answer maybe 20% of the questions that actually matter. Once you learn to JOIN tables together, that number shoots up dramatically.
By the end of this lesson, you'll know exactly how to combine two tables in a single SELECT statement using an INNER JOIN, understand the logic behind why it works, and feel confident enough to extend that skill to more complex queries. If you're brand new to SELECT statements, consider reviewing SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing — this lesson picks up right where that one leaves off.
What you'll learn:
You should be comfortable with:
SELECT ... FROM ... WHERE queryBefore you write a single JOIN, it's worth spending two minutes understanding why data is split up in the first place. This isn't arbitrary — it's intentional, and understanding the reason makes JOIN logic click much faster.
Suppose you stored every piece of information about an order in one giant table:
| order_id | customer_name | customer_email | product_name | quantity | price |
|---|---|---|---|---|---|
| 1001 | Maria Santos | maria@example.com | Wireless Keyboard | 1 | 79.99 |
| 1002 | Maria Santos | maria@example.com | USB Hub | 2 | 29.99 |
| 1003 | David Kim | david@example.com | Wireless Keyboard | 1 | 79.99 |
Notice the problem: Maria's name and email appear twice. If Maria changes her email address, you'd need to update every row that belongs to her. Miss one row, and your data is inconsistent. This kind of redundancy is called a data anomaly, and databases are explicitly designed to avoid it.
The solution is to store each type of information in its own table, and then link the tables together using a shared identifier — a key. Here's the same information split properly:
customers table:
| customer_id | name | |
|---|---|---|
| 1 | Maria Santos | maria@example.com |
| 2 | David Kim | david@example.com |
orders table:
| order_id | customer_id | product_name | quantity | price |
|---|---|---|---|---|
| 1001 | 1 | Wireless Keyboard | 1 | 79.99 |
| 1002 | 1 | USB Hub | 2 | 29.99 |
| 1003 | 2 | Wireless Keyboard | 1 | 79.99 |
Now Maria's email is stored exactly once. The orders table doesn't repeat her name — it just stores her customer_id (the number 1). That customer_id column in the orders table is called a foreign key — it's a reference that points back to the customer_id column in customers, which is called the primary key of that table.
Key insight
A JOIN works by finding rows in two tables where the values in a shared column match. The database reads that customer_id = 1 in an order row and goes looking for the row in customers where customer_id is also 1, then glues those rows together into a combined result.
Here is the most fundamental JOIN you can write:
SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
Let's break this down piece by piece before we look at results.
FROM orders — This tells SQL which table to start with. Think of orders as your "left" table, the anchor.
JOIN customers — This names the second table you want to bring in. The keyword JOIN by itself is shorthand for INNER JOIN — the most common type.
ON orders.customer_id = customers.customer_id — This is the join condition, and it's the heart of the whole operation. It says: "match a row from orders to a row from customers only when their customer_id values are equal." The table.column notation (called dot notation) is essential here because both tables have a column named customer_id. Without specifying the table name, SQL wouldn't know which one you mean.
Tip
Always write your join condition using dot notation — table_name.column_name — even when column names don't overlap. It makes your queries dramatically easier to read and debug, especially as you add more tables later.
If you run the query above against the sample data, SQL returns this combined result:
| order_id | customer_id | product_name | quantity | price | customer_id | name | |
|---|---|---|---|---|---|---|---|
| 1001 | 1 | Wireless Keyboard | 1 | 79.99 | 1 | Maria Santos | maria@example.com |
| 1002 | 1 | USB Hub | 2 | 29.99 | 1 | Maria Santos | maria@example.com |
| 1003 | 2 | Wireless Keyboard | 1 | 79.99 | 2 | David Kim | david@example.com |
Every order now has customer information attached to it. Notice that customer_id appears twice — once from each table. This is why SELECT * on JOIN queries gets messy fast. You'll want to select specific columns instead.
Instead of SELECT *, let's pick exactly the columns we want. When columns come from different tables, you use dot notation to specify which table each column belongs to:
SELECT
orders.order_id,
customers.name,
customers.email,
orders.product_name,
orders.quantity,
orders.price
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;
Result:
| order_id | name | product_name | quantity | price | |
|---|---|---|---|---|---|
| 1001 | Maria Santos | maria@example.com | Wireless Keyboard | 1 | 79.99 |
| 1002 | Maria Santos | maria@example.com | USB Hub | 2 | 29.99 |
| 1003 | David Kim | david@example.com | Wireless Keyboard | 1 | 79.99 |
Now that's a clean, readable result. Each row tells a complete story: who ordered what, how many, and at what price.
Writing orders.order_id and customers.name over and over becomes tedious in longer queries. SQL lets you assign short aliases to table names using the AS keyword (or just a space), which you can then use as shorthand throughout the query:
SELECT
o.order_id,
c.name,
c.email,
o.product_name,
o.quantity,
o.price
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id;
Here, o is an alias for orders and c is an alias for customers. This query produces the exact same result as the previous one — the aliases are just shorthand that exist within this single query. Using aliases well is a hallmark of clean, professional SQL. You can learn more about naming strategies in Using SQL Aliases Effectively: Naming Columns and Tables for Readable, Maintainable Queries.
Note
Once you define a table alias in the FROM clause, you must use it consistently throughout that query. You can't write FROM orders AS o and then refer to orders.order_id later — SQL will throw an error.
The JOIN keyword you've been using is specifically an INNER JOIN. The word "inner" describes its matching behavior: only rows that have a match in both tables are included in the result.
Let's make this concrete. Add a customer to our customers table who hasn't placed any orders yet:
| customer_id | name | |
|---|---|---|
| 1 | Maria Santos | maria@example.com |
| 2 | David Kim | david@example.com |
| 3 | Priya Nair | priya@example.com |
Now run the same INNER JOIN. Priya won't appear in the results at all — there are no orders with customer_id = 3, so there's nothing to match against. INNER JOIN only produces rows where both sides have matching data.
This behavior is often exactly what you want. But sometimes you do want to see all customers, even those with no orders — perhaps to identify inactive accounts. That's where LEFT JOIN comes in. For now, focus on mastering INNER JOIN; you'll explore the full landscape of join types in SQL JOINs Explained with Real-World Examples and get guidance on choosing the right one in Choosing the Right JOIN Type: When to Use INNER, LEFT, and FULL OUTER JOIN for Clean Aggregation Results.
Warning
A common beginner mistake is assuming a JOIN always returns all rows from both tables. It doesn't. An INNER JOIN silently drops any row that has no match on the other side. If your result looks smaller than expected, check whether your join condition is working correctly and whether there are unmatched rows.
One of the most powerful things you can do once you have a JOIN working is layer in a WHERE clause to filter the combined result down to exactly what you need. The WHERE clause applies after the join logic runs, so it filters the already-combined rows.
Going back to the original scenario: your manager wants to see orders placed last month. Let's add a date column to the orders table and filter accordingly:
SELECT
o.order_id,
c.name,
c.email,
o.product_name,
o.quantity,
o.price,
o.order_date
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-11-01'
AND o.order_date < '2024-12-01';
The join happens first (combining orders with customer data), and then WHERE narrows the results to only November 2024 orders. You can filter on any column from either table — just remember to prefix with the alias when column names might be ambiguous.
You can also filter on specific customers:
SELECT
o.order_id,
c.name,
o.product_name,
o.price
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
WHERE c.name = 'Maria Santos';
This returns only orders belonging to Maria. The WHERE clause references c.name — a column from the customers table — even though we started with FROM orders. Once the join creates the combined rows, all columns from both tables are available to filter on.
For a deeper dive into filtering techniques, Advanced SQL Filtering and Sorting: Mastering WHERE, ORDER BY, and Query Optimization covers WHERE with sorting in detail.
Once you're comfortable with two tables, adding a third follows the exact same pattern — you just chain another JOIN onto the query. Let's expand our example with a products table that holds additional product details:
products table:
| product_id | product_name | category | supplier |
|---|---|---|---|
| P01 | Wireless Keyboard | Peripherals | TechSupply |
| P02 | USB Hub | Peripherals | TechSupply |
Updated orders table (now with a product_id column):
| order_id | customer_id | product_id | quantity | price | order_date |
|---|---|---|---|---|---|
| 1001 | 1 | P01 | 1 | 79.99 | 2024-11-05 |
| 1002 | 1 | P02 | 2 | 29.99 | 2024-11-12 |
| 1003 | 2 | P01 | 1 | 79.99 | 2024-11-18 |
SELECT
o.order_id,
c.name AS customer_name,
p.product_name,
p.category,
o.quantity,
o.price,
o.order_date
FROM orders AS o
JOIN customers AS c ON o.customer_id = c.customer_id
JOIN products AS p ON o.product_id = p.product_id
WHERE o.order_date >= '2024-11-01';
Each additional JOIN follows the same JOIN table AS alias ON matching_condition pattern. SQL processes them sequentially, building up a wider and wider combined row with each JOIN. The order generally doesn't change the result for INNER JOINs, but convention is to list tables in a logical flow from your starting point.
Tip
When you're building a multi-table query, start simple. Write the two-table JOIN first, confirm it looks right, then add the third JOIN. Debugging a broken three-table query is much harder than debugging a broken two-table query. Build incrementally and validate each step.
Set up these three tables in any SQL environment (SQLite via DB Fiddle at dbfiddle.uk works great for this):
CREATE TABLE departments (
dept_id INTEGER PRIMARY KEY,
dept_name TEXT
);
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
full_name TEXT,
dept_id INTEGER,
hire_date TEXT
);
CREATE TABLE projects (
project_id INTEGER PRIMARY KEY,
project_name TEXT,
dept_id INTEGER,
budget DECIMAL(10,2)
);
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Marketing'), (30, 'Finance');
INSERT INTO employees VALUES
(1, 'Aisha Thompson', 10, '2021-03-15'),
(2, 'Carlos Vega', 10, '2022-07-01'),
(3, 'Nadia Osei', 20, '2020-11-20'),
(4, 'James Liu', 30, '2023-01-10'),
(5, 'Sarah Park', 20, '2019-06-05');
INSERT INTO projects VALUES
(101, 'API Redesign', 10, 150000),
(102, 'Brand Refresh', 20, 45000),
(103, 'Tax Automation', 30, 72000),
(104, 'Mobile App', 10, 200000);
Your tasks:
Write a query that shows each employee's full name alongside their department name. Use aliases for both tables.
Extend that query to also show projects that belong to the same department. Each employee should appear alongside their department's project names.
Add a WHERE clause to show only Engineering employees and their associated projects.
Challenge: Modify the SELECT list to also show the project budget, and add an ORDER BY clause to sort results by employee hire date.
Expected output for task 3 (columns: full_name, dept_name, project_name):
| full_name | dept_name | project_name |
|---|---|---|
| Aisha Thompson | Engineering | API Redesign |
| Aisha Thompson | Engineering | Mobile App |
| Carlos Vega | Engineering | API Redesign |
| Carlos Vega | Engineering | Mobile App |
Forgetting the ON clause (or getting it backwards)
-- WRONG: This is a CROSS JOIN - produces every combination of rows
SELECT * FROM orders JOIN customers;
-- RIGHT: Always specify the join condition
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
A query without ON is called a cross join — it multiplies every row in one table by every row in the other. With 500 customers and 10,000 orders, that's 5,000,000 rows. Your query will run forever (or crash). Always include ON.
Ambiguous column names without dot notation
-- WRONG: SQL doesn't know which table's customer_id you mean
SELECT customer_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
-- RIGHT: Be explicit
SELECT orders.customer_id FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
Joining on the wrong columns
Joining orders.order_id = customers.customer_id will silently produce garbage results (or no results at all) if the values happen not to match. Always double-check that the columns you're joining on represent the same concept — usually a primary key on one side and a foreign key on the other. If your results look wrong, check out Debugging SQL Queries: How to Read Error Messages, Trace Wrong Results, and Fix Broken Joins Step by Step.
Getting more rows than expected
If a foreign key column has duplicate values on the "many" side of a relationship, your result will have multiple rows per parent record — this is correct behavior, but it surprises beginners. For example, joining one customer to three orders produces three rows for that customer. That's exactly right. It becomes a problem only if you expected one row per customer.
Warning
Be careful about filtering in the ON clause versus the WHERE clause. For INNER JOINs they often produce the same result, but once you start using LEFT JOINs, the placement of your filter condition dramatically changes your output. Keep your join logic in ON and your row-filtering logic in WHERE until you understand the difference deeply.
NULL values causing missed matches
If a customer_id in your orders table is NULL (perhaps an order was placed by a guest), the INNER JOIN will silently drop that row — NULL never equals anything, including another NULL. If you need to handle NULLs carefully, NULL Handling in SQL: IS NULL, COALESCE, and NULLIF has you covered.
You've covered a lot of ground. Here's the core of what you now know:
JOIN combines rows from two tables wherever a shared column's values match.INNER JOIN only returns rows that have a match on both sides.table1.column = table2.column.AS o, AS c) keep your queries readable.The JOIN you learned today is the foundation for a large fraction of real analytical SQL work. From here, the most logical next steps are:
The jump from single-table queries to multi-table queries is genuinely one of the biggest leaps in SQL. You've made it.
SQL Fundamentals