Joins

A SQL JOIN combines rows from two or more tables based on a related column between them, letting you pull connected data — like customers and their orders — into a single result.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

By the end of this lesson you'll be able to combine data that's split across two tables — pairing customers with their orders, keeping unmatched rows when you need them, and understanding exactly where those NULL s come from. JOINs are the skill that turns isolated tables into real answers.

What You'll Learn

Our Two Sample Tables

JOINs always involve two or more tables , so this lesson uses two. Every query below runs against these exact rows — keep them in mind as you read.

orders — what they bought (note customer_id links back to customers.id )

Two details that make the examples interesting: Marie Curie (id 5) has no orders at all, and order #105 points at customer_id 99 , who isn't in the customers table. Watch how each JOIN type treats these two "loose ends".

1. Why You Need a JOIN

Databases deliberately split data across tables to avoid repeating themselves. A customer's name lives once in customers ; each order just stores that customer's id instead of copying the whole name. That keeps data tidy — but it means the orders table alone can't tell you who placed an order.

A JOIN stitches the tables back together for the duration of one query, matching rows by a shared value (here, the customer id). Nothing is changed on disk — the combined table exists only in the result.

Imagine two stacks of paper on your desk: a list of customers (each with an account number) and a pile of receipts (each stamped with the account number, but no name). To bill people you walk down the receipts and, for each one, find the matching customer by account number. A JOIN is that matching done automatically — the ON condition is the rule "match where the account numbers are equal".

2. INNER JOIN — Only the Matches

An INNER JOIN returns only the rows that have a match in both tables. Each orders row is paired with the customers row whose id equals the order's customer_id . Rows without a partner on either side are dropped.

Two pieces of new syntax do the work. Table aliases ( FROM customers c , JOIN orders o ) give each table a short nickname so you can write c.name and o.product instead of the full table name. The ON condition states the matching rule — usually one table's primary key = another table's foreign key .

Your Turn: complete the INNER JOIN

Fill in the blanks to show each buyer's city next to the product they ordered. The expected result is in the comments so you can check yourself.

3. LEFT JOIN — Keep Every Left-Side Row

A LEFT JOIN keeps every row from the left table (the one in FROM ), whether or not it finds a match on the right. Where there's no match, the right-side columns come back as NULL — SQL's marker for "no value / unknown".

This is exactly what you want when "missing" is meaningful. Marie Curie has never ordered, so an INNER JOIN would hide her — but a LEFT JOIN keeps her, with NULL where her order details would be.

A classic follow-up is to isolate just the gaps — the customers with no orders — by keeping only the rows where the matched order is NULL :

Your Turn: complete the LEFT JOIN

Fill in the blanks so the result lists every customer's order amount — including the customer who has never ordered (their amount should be NULL ).

4. RIGHT JOIN — The Mirror Image

A RIGHT JOIN is a LEFT JOIN seen in a mirror: it keeps every row from the right table and fills the left side with NULL when there's no match. With customers RIGHT JOIN orders , every order survives — including the orphan order #105 whose customer_id 99 matches nobody, so its name is NULL .

5. FULL OUTER JOIN — Keep Everything

A FULL OUTER JOIN combines both behaviours: it keeps every row from both tables. Matched rows line up normally; unmatched rows on either side appear with NULL s filling the missing columns. It's the join you reach for when you want a complete picture and need to spot loose ends on either side.

With our data you get all five customers and all five orders: order-less Marie Curie shows up with NULL product/amount, and orphan order #105 shows up with a NULL name.

Common Errors (and the fix)

• Forgetting ON → a cross join "explosion": FROM customers c JOIN orders o with no ON pairs every customer with every order. Our 5×5 tables would balloon to 25 rows; on real tables that's millions. Always state the matching rule: ON c.id = o.customer_id .
• "ambiguous column name: id": both tables have an id , so SQL doesn't know which you mean. Prefix it with the table or alias — c.id or o.id , never a bare id .
• Using INNER when you meant LEFT : an INNER JOIN silently drops customers with no orders. If a customer "disappeared" from your report, you probably wanted a LEFT JOIN to keep unmatched rows.
• Filtering on a NULL with = : WHERE o.id = NULL never matches anything. To find unmatched rows after a LEFT JOIN , use WHERE o.id IS NULL .
• "no such column: customers.name": once a table has an alias ( customers c ), you must use the alias — write c.name , not customers.name .

📘 Quick Reference

Join

Keeps

NULLs appear for…

Shape: SELECT c.name, o.product FROM customers c <JOIN> orders o ON c.id = o.customer_id;

Frequently Asked Questions

Q: What's the difference between JOIN and INNER JOIN ?

Nothing — JOIN on its own means INNER JOIN in every major database. Writing INNER explicitly just makes your intent clearer.

The one whose rows you want to keep no matter what. customers LEFT JOIN orders keeps all customers; the order is a deliberate choice, not a rule.

Yes — chain them: ... JOIN orders o ON ... JOIN order_items oi ON ... . Each JOIN adds one more link, each with its own ON condition. You'll meet this in later lessons.

If a customer has two orders, they appear twice — once per matching order (see Ada Lovelace above). That's correct: the result has one row per matched pair , not one per customer.

Mini-Challenge: Big-Ticket Orders

Put it all together — a brief, a blank canvas, and the expected result in the comments. Write it, then copy it into a playground to confirm. (Hint: join customers to orders, then add a WHERE .)

🎉 Lesson Complete

• ✅ Data is split across tables; a JOIN recombines it on a shared value
• ✅ INNER JOIN keeps only rows matched in both tables
• ✅ LEFT / RIGHT JOIN keep all rows from one side; FULL OUTER keeps both
• ✅ Unmatched rows fill their missing columns with NULL
• ✅ The ON condition is the matching rule; table aliases ( c , o ) keep it readable
• ✅ Next: Aggregate Functions — count, sum, and average across the rows a JOIN produces

Practice quiz

Continue this course

• Previous: ORDER BY & Sorting
• Next: Aggregate Functions