JOIN Operations

Reviewed & published by Brayan K

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 NULLs come from. JOINs are the skill that turns isolated tables into real answers.

What You'll Learn

• Why data is split across tables and needs joining
• Match rows with INNER JOIN and an ON condition
• Keep every left-side row with LEFT JOIN
• Mirror it with RIGHT JOIN and keep everything with FULL OUTER JOIN
• Use table aliases (c, o) to keep queries readable
• Understand why unmatched rows produce NULLs

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.

customers — who they are

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

-- The two tables this lesson uses. Run this block first and
-- every example further down the page has real rows to join.
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
INSERT INTO customers VALUES (1, 'Ada Lovelace',   'London');
INSERT INTO customers VALUES (2, 'Grace Hopper',   'New York');
INSERT INTO customers VALUES (3, 'Alan Turing',    'Manchester');
INSERT INTO customers VALUES (4, 'Linus Torvalds', 'Portland');
INSERT INTO customers VALUES (5, 'Marie Curie',    'Paris');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, product TEXT, amount REAL);
INSERT INTO orders VALUES (101,  1, 'Keyboard', 75.00);
INSERT INTO orders VALUES (102,  2, 'Monitor',  199.99);
INSERT INTO orders VALUES (103,  1, 'Mouse',    24.50);
INSERT INTO orders VALUES (104,  3, 'Desk',     120.00);
INSERT INTO orders VALUES (105, 99, 'Webcam',   45.00);

-- The data you want lives in TWO tables.
-- The orders table only stores customer_id — not the name.
SELECT * FROM orders;

-- To show "Ada Lovelace bought a Keyboard for 75.00"
-- you must connect orders to customers on the shared id.
-- That connection is a JOIN.

-- ✅ Expected result:
-- id | customer_id | product | amount
-- 101 | 1 | Keyboard | 75
-- 102 | 2 | Monitor | 199.99
-- 103 | 1 | Mouse | 24.5
-- 104 | 3 | Desk | 120
-- 105 | 99 | Webcam | 45

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.

-- Uses the customers and orders tables from the first example.
-- INNER JOIN: keep only rows that MATCH in both tables
SELECT
    c.name,            -- from customers (aliased c)
    o.product,         -- from orders    (aliased o)
    o.amount
FROM customers c
INNER JOIN orders o
    ON c.id = o.customer_id;   -- the matching rule

-- c and o are table aliases — short nicknames so you
-- don't retype "customers"/"orders" on every column.
-- ON says HOW the two tables line up: a customer row
-- pairs with an order row when their ids are equal.

-- ✅ Expected result:
-- name | product | amount
-- Ada Lovelace | Keyboard | 75
-- Grace Hopper | Monitor | 199.99
-- Ada Lovelace | Mouse | 24.5
-- Alan Turing | Desk | 120

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.

-- 🎯 YOUR TURN — fill in the blanks, then press "Try it Yourself"
-- Goal: show each customer's city next to the product they bought.

SELECT c.name, c.city, o.product
FROM customers c
___ JOIN orders o          -- 👉 the join type that keeps only matches
    ON c.id = ___;         -- 👉 the orders column that points back to a customer

-- ✅ Expected result: 5 matched rows (only customers WITH orders),
--    e.g. Ada Lovelace | London | Keyboard ,  Grace Hopper | New York | Monitor , ...

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.

-- Uses the customers and orders tables from the first example.
-- LEFT JOIN: keep ALL rows from the LEFT table (customers),
-- and attach order data where it exists. No match -> NULLs.
SELECT
    c.name,
    o.product,
    o.amount
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id;

-- Marie Curie has placed no orders, so her row still appears
-- but product and amount come back as NULL (empty / unknown).

-- ✅ Expected result:
-- name | product | amount
-- Ada Lovelace | Keyboard | 75
-- Ada Lovelace | Mouse | 24.5
-- Grace Hopper | Monitor | 199.99
-- Alan Turing | Desk | 120
-- Linus Torvalds | NULL | NULL
-- Marie Curie | NULL | NULL

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:

-- A classic use of LEFT JOIN: find the UNMATCHED rows.
-- "Which customers have never ordered anything?"
SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.id IS NULL;   -- keep only rows where no order matched

-- IS NULL is the filter that isolates the gaps a LEFT JOIN reveals.

-- ✅ Expected result:
-- name | city
-- Linus Torvalds | Portland
-- Marie Curie | Paris

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

-- 🎯 YOUR TURN — fill in the blanks.
-- Goal: list EVERY customer and their order amount —
-- including the customer who has never ordered (amount = NULL).

SELECT c.name, o.amount
FROM customers c
___ JOIN orders o          -- 👉 the join type that keeps ALL customers
    ON c.id = o.customer_id;

-- ✅ Expected result: 6 rows — all 5 customers (one appears twice
--    because they have 2 orders) PLUS Marie Curie with amount = 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.

-- Uses the customers and orders tables from the first example.
-- RIGHT JOIN: the mirror image — keep ALL rows from the RIGHT
-- table (orders) and attach customer data where it exists.
SELECT
    c.name,
    o.product,
    o.amount
FROM customers c
RIGHT JOIN orders o
    ON c.id = o.customer_id;

-- Order #105 was placed by customer_id 99, who isn't in our
-- customers table, so its name comes back NULL — an "orphan" order.

-- ✅ Expected result:
-- name | product | amount
-- Ada Lovelace | Keyboard | 75
-- Ada Lovelace | Mouse | 24.5
-- Grace Hopper | Monitor | 199.99
-- Alan Turing | Desk | 120
-- NULL | Webcam | 45

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

-- Uses the customers and orders tables from the first example.
-- FULL OUTER JOIN: keep EVERYTHING from both tables.
-- Matched rows line up; unmatched rows on either side show NULL.
SELECT
    c.name,
    o.product,
    o.amount
FROM customers c
FULL OUTER JOIN orders o
    ON c.id = o.customer_id;

-- You get every customer (even order-less Marie Curie) AND
-- every order (even the orphan #105 with no customer).

-- ✅ Expected result:
-- name | product | amount
-- Ada Lovelace | Keyboard | 75
-- Ada Lovelace | Mouse | 24.5
-- Grace Hopper | Monitor | 199.99
-- Alan Turing | Desk | 120
-- Linus Torvalds | NULL | NULL
-- Marie Curie | NULL | NULL
-- NULL | Webcam | 45

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

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.

Q: Which table should go "left"?

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.

Q: Can I join more than two tables?

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.

Q: Why do I get duplicate-looking rows?

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

-- 🎯 MINI-CHALLENGE
-- Using only what this lesson covered (a JOIN + ON, a WHERE filter,
-- and choosing columns):
--   1. Join customers to orders so each order shows the buyer's name.
--   2. Keep only orders where amount is greater than 50.
--   3. Return three columns: name, product, amount.
--
-- ✅ Expected result: 3 rows
--    Ada Lovelace  | Keyboard | 75.00
--    Grace Hopper  | Monitor  | 199.99
--    Alan Turing   | Desk     | 120.00

-- your query here

🎉 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: Checkpoint: Building a Schema
• Next: Aggregate Functions — Calculate totals, averages, and counts with SUM, AVG, COUNT, MIN, MAX
• Quick reference: SQL cheat sheet › More Joins
• From the blog: SQL Joins Explained With Examples

Related lessons

• ORDER BY & Sorting