SQL Joins Explained with Simple Examples

Sooner or later every SQL learner hits the same wall: you understand SELECT, WHERE and ORDER BY, and then someone says "just join the two tables" and your brain goes blank. If those basics aren't second nature yet, start with SQL for beginners: the 10 queries to learn first.

SQL joins feel hard because most explanations jump straight to Venn diagrams and jargon. This guide does the opposite. We'll use two tiny tables, run five queries, and look at exactly what comes back each time. By the end you'll know which join to reach for and why.

Why do we need joins at all?

Good databases split data into separate tables so nothing is stored twice. Customer details live in a customers table. Purchases live in an orders table. Each order only stores a customer_id pointing back to the customer.

That's tidy for storage, but when you want to answer "who bought what?" you need data from both tables in one result. A join combines rows from two tables based on a related column.

Our example data

Everything below uses these two tables.

customers

orders

Notice the deliberate quirks:

• Ava has two orders.
• Chloe and Dev have no orders.
• Order 104 belongs to customer 99, who doesn't exist in customers.

These edge cases are what make the different joins behave differently.

Want to follow along? Here's the setup script:

CREATE TABLE customers (id INT, name TEXT);
INSERT INTO customers VALUES (1,'Ava'), (2,'Ben'), (3,'Chloe'), (4,'Dev');
 
CREATE TABLE orders (id INT, customer_id INT, item TEXT);
INSERT INTO orders VALUES
  (101, 1, 'Laptop'), (102, 2, 'Keyboard'),
  (103, 1, 'Mouse'),  (104, 99, 'Monitor');

The anatomy of a join

Every join has the same shape:

SELECT ...
FROM table_a
<JOIN TYPE> table_b
  ON table_a.some_column = table_b.some_column;

The ON clause is the matching rule: "pair up a row from A with a row from B when these columns are equal." The join type decides what happens to rows that don't find a partner.

That's the whole secret. Matching rows are always kept. Join types only differ in how they treat the leftovers.

1. INNER JOIN: only the matches

INNER JOIN returns rows that have a match in both tables. Everything else is dropped.

SELECT c.name, o.item
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Chloe and Dev vanished because they have no orders. The Monitor vanished because its customer doesn't exist. Ava appears twice because she matches two orders.

Use it when you only care about records that exist on both sides, such as "show me every order along with the customer who placed it."

JOIN on its own means INNER JOIN. Most people just write JOIN.

2. LEFT JOIN: everything from the left table

LEFT JOIN keeps all rows from the left table (the one after FROM), plus matching rows from the right. Where there's no match, the right-side columns are filled with NULL.

SELECT c.name, o.item
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Now Chloe and Dev are back, with NULL for item. The orphaned Monitor is still missing, because it isn't in the left table.

Use it when you want "everything from A, and B's data if it exists", such as listing all customers including those who haven't ordered yet.

Bonus: find rows with no match (the anti-join)

Add a WHERE ... IS NULL filter to a left join and you get only the leftovers:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

This answers "which customers have never ordered?" It's one of the most useful patterns in real-world SQL.

3. RIGHT JOIN: everything from the right table

RIGHT JOIN is a LEFT JOIN mirrored. It keeps all rows from the right table and fills in NULL on the left where nothing matches.

SELECT c.name, o.item
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

The Monitor is back (with no customer name), and Chloe and Dev are gone.

Tip: Most developers rarely write RIGHT JOIN. You can always swap the table order and use LEFT JOIN, which reads more naturally from top to bottom. Also note that SQLite only added RIGHT and FULL joins in version 3.39, so very old setups may not support them.

4. FULL OUTER JOIN: keep everything

FULL OUTER JOIN keeps all rows from both tables. Matches are paired up, and anything without a partner is kept with NULLs on the missing side.

SELECT c.name, o.item
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Customers without orders and orders without customers all show up.

Use it when you're auditing or reconciling two datasets and want to see everything that doesn't line up on either side. MySQL doesn't support FULL OUTER JOIN directly; the usual workaround is a LEFT JOIN combined with a RIGHT JOIN using UNION.

5. CROSS JOIN: every combination

CROSS JOIN has no ON clause. It pairs every row of one table with every row of the other.

SELECT c.name, o.item
FROM customers c
CROSS JOIN orders o;

With 4 customers and 4 orders, you get 4 × 4 = 16 rows.

That sounds odd, but it's handy for generating combinations, like every product in every size, or every date for every store. Just be careful: cross joining two big tables can produce an enormous result.

Quick reference: which join should I use?

A simple way to decide:

1. Do I only want rows that exist in both tables? → INNER JOIN
2. Do I want everything from one table, even without a match? → LEFT JOIN (put that table first)
3. Do I need to see mismatches on both sides? → FULL OUTER JOIN

Common mistakes to avoid

Forgetting the ON clause. In some databases this silently turns your query into a cross join and floods you with rows. If your result count looks wildly too big, check your join condition first.

Filtering a LEFT JOIN in WHERE. If you write LEFT JOIN orders o ... WHERE o.item = 'Laptop', rows with NULL items are filtered out, and your left join quietly behaves like an inner join. Put conditions on the right table in the ON clause instead:

SELECT c.name, o.item
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id AND o.item = 'Laptop';

Ambiguous column names. If both tables have a column called id, writing just id will throw an error. Use table aliases (c.id, o.id) as we did above.

Unexpected duplicates. Joining one customer to many orders repeats the customer on every row. That's correct behavior, but if you then SUM a customer-level number, you'll count it multiple times. Aggregate carefully.

Joining more than two tables

Joins chain naturally. Add one more JOIN for each extra table:

SELECT c.name, o.item, p.price
FROM customers c
JOIN orders o   ON o.customer_id = c.id
JOIN products p ON p.name = o.item;

Read it left to right: join customers to orders, then join that result to products.

Practice makes it stick

Reading about joins helps, but they only really click once you've written a few yourself. Try these on the sample data:

1. List every customer and the number of orders they've placed (hint: LEFT JOIN + COUNT + GROUP BY).
2. Find all orders that don't belong to a real customer.
3. Show customers who have ordered a Mouse.

Ready for more hands-on practice? Start with Joining Two Tables (opens in a new tab), then continue with Keeping Unmatched Rows With Left Join (opens in a new tab) and Joining More Than Two Tables (opens in a new tab) in our Learn SQL (opens in a new tab) course, where you write queries in the browser and get instant feedback.

Key takeaways

• A join combines rows from two tables using a matching condition in ON.
• Matching rows are always kept. Join types only decide what happens to unmatched rows.
• INNER JOIN keeps matches only; LEFT/RIGHT keep one full side; FULL OUTER keeps both.
• LEFT JOIN ... WHERE right.id IS NULL is the go-to pattern for "find rows with no match."
• When in doubt, start with a tiny dataset and check the result row by row, just like we did here.

Once joins feel natural, the rest of SQL opens up: subqueries, aggregations across tables and real analytical queries all build on them. For GROUP BY, HAVING and the other everyday queries, see our guide to the 10 SQL queries to learn first.

These courses give you the hands-on practice to make joins second nature and a first language to build with:

Check out our related courses

Beginner

Learn SQL

Use SQL to query a real database. Learn SELECT, filtering, sorting, grouping, joins, and how to create and change tables.

56 lessons

Instructor

Brad Traversy

4.8

7 enrolled

Beginner

Python for Beginners

Go from zero to writing real Python programs in the browser. Build up the core language, variables, types, control flow, lists, loops, and functions, with instant feedback and no setup required.

81 lessons

Instructor

Dennis Ivy

4.9

995 enrolled