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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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".

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".

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 .

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.

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 :

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 ).

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 .

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.

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: . 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.

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 .)

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary