SQL JOINs Explained: Interactive Lesson with Exercises

Your path

10 steps · 2 quizzes · about 60 minutes · open any step, or read it here

1. 1
   SQL JOINs: combine two tablesStart hereIn this lesson you practise SQL joins on two small tables: customers and the orders they placed. You will see which rows each join keeps, which it leaves out, and why, then write joins of your own and check the answers. Here is the whole idea in one picture.InteractiveHands-on · 4 min

   Read this step

   In this lesson you practise SQL joins on two small tables: customers and the orders they placed. You will see which rows each join keeps, which it leaves out, and why, then write joins of your own and check the answers.

   Here is the whole idea in one picture. Each line is a pair of rows that match: an order and the customer it belongs to. Press the join types and watch which rows make it into the result.

   Interactive · Which rows meet in each join — try it in the lesson

   In this lesson you'll

   • connect two tables through a key: customers.id and orders.customer_id
   • write INNER, LEFT, RIGHT, FULL OUTER and CROSS joins and predict their row counts
   • find what is missing: customers who never ordered, orders with no customer
   • total and count joined rows without the classic mistakes

   About 60 minutes, with checks along the way, a practice set and a final quiz.

2. 2
   Two tables, one keyReal databases keep each fact once. A customer's name and city live in customers; each purchase lives in orders. The two are connected by a number: customers.id is the primary key: every customer has a different id.InteractiveHands-on · 4 min

   Read this step

   Real databases keep each fact once. A customer's name and city live in customers; each purchase lives in orders. The two are connected by a number:

   • customers.id is the primary key: every customer has a different id.
   • orders.customer_id is a foreign key: it holds the id of the customer who placed the order.

   So order 101 with customer_id 1 belongs to customer 1, Ava. Matching colours in the tables below show the same key.

   Try it: tap Ava, then Dana, then order 105.

   Interactive · Customers, orders and the key between them — try it in the lesson

   Two rows have no partner, and they will matter all lesson:

   • Dana and Eli have no orders yet.
   • Order 105 has customer_id 7, and there is no customer 7. Maybe the account was deleted. Real data has rows like this.

   Takeaway: a join pairs rows whose keys are equal. Rows with no partner are where the join types differ.

3. 3
   INNER JOIN: only rows that matchINNER JOIN returns one row for every pair that matches the ON condition, and nothing else.InteractiveHands-on · 5 min

   Read this step

   INNER JOIN returns one row for every pair that matches the ON condition, and nothing else.

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

   Read it in order:

   1. FROM customers c: start from customers, and call it c for short.
   2. INNER JOIN orders o: bring in orders, called o.
   3. ON c.id = o.customer_id: a customer and an order belong together when these are equal.
   4. SELECT c.name, o.id, o.amount: the columns to show from the pairs.

   Try it: press Run and watch the lines. Then change o.amount to c.city and run it again.

   Interactive · INNER JOIN playground — try it in the lesson

   Ava appears twice because she placed two orders: one row per matching pair. Dana, Eli and order 105 have no partner, so INNER JOIN leaves them out. JOIN on its own means INNER JOIN.

   Check your work · Add `WHERE o.amount > 100` after the ON line and press **Run**. How many rows come back? Paste the number. — check it in the lesson

   Takeaway: INNER JOIN keeps matching pairs only; a row that matches twice appears twice.

4. 4
   Short names and the column you meantWhen two tables are in a query, SQL needs to know which table each column comes from. Both tables have a column called id, so SELECT id is ambiguous and the database refuses it. Write table.column: customers.name, orders.id.InteractiveHands-on · 4 min

   Read this step

   When two tables are in a query, SQL needs to know which table each column comes from. Both tables have a column called id, so SELECT id is ambiguous and the database refuses it.

   • Write table.column: customers.name, orders.id.
   • Or give each table a short name (an alias) after it, customers c, and write c.name. Once a table has an alias, use the alias: customers.name no longer works in PostgreSQL.
   • c.* means every column of customers.

   Try it: press the three buttons under the query and read each message. Then fix the query so it shows each order's id, amount and the customer's city.

   Interactive · Which id did you mean? — try it in the lesson

   Takeaway: prefix every column in a join with its table's alias. It costs two characters and stops a whole class of mistakes.

5. 5
   LEFT JOIN: keep every row on the leftSometimes the rows with no match are the point. A list of all customers with their orders must still show Dana and Eli. LEFT JOIN keeps every row of the table on its left (the one after FROM).InteractiveHands-on · 5 min

   Read this step

   Sometimes the rows with no match are the point. A list of all customers with their orders must still show Dana and Eli.

   LEFT JOIN keeps every row of the table on its left (the one after FROM). When a left row has no match, its columns from the right table come back as NULL, "no value".

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

   Try it: switch between INNER and LEFT and compare the row counts. Then swap the two tables (FROM orders o LEFT JOIN customers c …) and run it: which row is kept with NULLs now?

   Interactive · LEFT JOIN playground — try it in the lesson

   Left means "the table written first". Swap the tables and you keep every order instead: order 105 comes back with a NULL name, and Dana and Eli are gone.

   Takeaway: LEFT JOIN = every row from the left table, plus its matches, NULLs where there are none. It is the join you will use most after INNER.

6. Check: INNER and LEFTUse the customers and orders tables from the lesson.Quiz · 4 questions

   Read the questions

   Use the customers and orders tables from the lesson.

   Question 1. What does INNER JOIN return?

   • Every row from both tables
   • Only pairs of rows that match the ON condition
   • Every row from the first table
   • Only rows that appear in neither table

   Question 2. Dana has no orders. In `customers c LEFT JOIN orders o ON c.id = o.customer_id`, what happens to her?

   • She is left out
   • She appears once, with NULL in the order columns
   • She appears once for every order in the table
   • The query fails

   Question 3. Which column connects an order to its customer?

   • orders.id
   • orders.customer_id, which holds a customers.id
   • customers.name
   • orders.amount

   Question 4. Ava placed two orders. How many rows does Ava get in the INNER JOIN?

   • 1
   • 2
   • 3
   • 0
7. 6
   RIGHT and FULL OUTER JOINTwo more joins keep unmatched rowsInteractiveHands-on · 5 min

   Read this step

   Two more joins keep unmatched rows:

   Join	Keeps unmatched rows from	Rows here
   INNER JOIN	neither table	5
   LEFT JOIN	the left table (customers)	7
   RIGHT JOIN	the right table (orders)	6
   FULL OUTER JOIN	both tables	8

   RIGHT JOIN is a LEFT JOIN read from the other side: customers c RIGHT JOIN orders o gives the same rows as orders o LEFT JOIN customers c. Most teams write LEFT JOIN and put the table they want to keep first, so queries read the same way.

   FULL OUTER JOIN keeps everything: every match, plus Dana and Eli with NULL orders, plus order 105 with a NULL customer. It is how you compare two lists and see what is missing from either side. MySQL has no FULL OUTER JOIN; there you combine a LEFT and a RIGHT join with UNION.

   Try it: press each join type and predict the row count before you look.

   Interactive · Four joins, one pair of tables — try it in the lesson

   A note on the Venn diagrams you may have seen for joins: they show which rows are kept, but not that one customer can match many orders. Ava's two rows are why counting the circles doesn't give the row count.

   Takeaway: INNER keeps matches, LEFT and RIGHT also keep one side's leftovers, FULL keeps both sides' leftovers.

8. 7
   Find what's missingThe rows with NULLs answer real questions: which customers never ordered? Which orders point to a customer who doesn't exist? Do a LEFT JOIN, then keep only the rows where the right side found nothingInteractiveHands-on · 5 min

   Read this step

   The rows with NULLs answer real questions: which customers never ordered? Which orders point to a customer who doesn't exist?

   Do a LEFT JOIN, then keep only the rows where the right side found nothing:

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

   Use IS NULL, never = NULL. NULL means "unknown", and comparing anything with unknown gives unknown, so WHERE o.id = NULL returns no rows at all.

   The trap: WHERE after a LEFT JOIN. WHERE runs after the join. A test on a right-table column, like WHERE o.amount > 100, is unknown for the NULL rows, so it throws away the very rows LEFT JOIN kept, and you are back to an inner join. To filter the orders but keep every customer, put the test in ON.

   Try it: run the four buttons and compare. Which ones still show Dana and Eli?

   Interactive · Find what's missing — try it in the lesson

   Takeaway: LEFT JOIN + WHERE right.id IS NULL finds what is missing. Conditions on the right table go in ON when you want to keep every left row.

9. 8
   How many rows will a join return?Before you trust a report, know how many rows the join should give. One to one: at most one match each side. The row count stays the same. One to many: a customer with three orders gives three rows.InteractiveHands-on · 5 min

   Read this step

   Before you trust a report, know how many rows the join should give.

   • One to one: at most one match each side. The row count stays the same.
   • One to many: a customer with three orders gives three rows. Totals of customer columns (say, a credit limit) are then counted three times.
   • No ON condition: CROSS JOIN pairs every row with every row. 5 customers × 6 orders = 30 rows. A join whose condition is wrong behaves the same way and can turn a small table into millions of rows.

   Try it: run each button and read the row count. Then write SELECT COUNT(*) FROM customers c CROSS JOIN orders o and run it.

   Interactive · Count the rows — try it in the lesson

   A quick check after any join: count the rows, and ask whether the number makes sense for the question.

   Takeaway: a join can only multiply rows through matches. If a count is bigger than you expected, look at the ON condition first.

10. 9
    Join, then summariseJoins and GROUP BY work together: join first, then fold the rows into one per group.InteractiveHands-on · 5 min

    Read this step

    Joins and GROUP BY work together: join first, then fold the rows into one per group.

    SELECT c.name, COUNT(o.id) AS orders, SUM(o.amount) AS total
    FROM customers c
    LEFT JOIN orders o ON c.id = o.customer_id
    GROUP BY c.name
    ORDER BY total DESC;
    

    Two details make this right:

    • LEFT JOIN, so customers with no orders still get a row.
    • COUNT(o.id), not COUNT(*). COUNT(*) counts rows, and Dana's NULL-filled row is a row, so she would get 1 order. COUNT(o.id) counts non-NULL values, so she gets 0.

    Dana's total is NULL: there is nothing to add up. In a real report you would write COALESCE(SUM(o.amount), 0) to show 0.

    Try it: run the query, then press COUNT(*) instead and see which customers change.

    Interactive · Join, then GROUP BY — try it in the lesson

    Check your work · Run the query. What is Chen's total? Paste the number. — check it in the lesson

    Takeaway: after an outer join, count a column from the right table, never *.