Database Management · Beginner level

Beginner level · Module 3: Keys, Relationships and Joins · Lesson 2 of 3

Combining Tables with INNER JOIN

Join related tables to show data from both, such as payments with student names.

In this lesson you will

  • Write INNER JOIN queries with ON conditions
  • Use table aliases and qualify column names

Key concepts

INNER JOINON conditiontable aliasesjoining three tables

The lesson

SELECT s.name, p.amount FROM payments AS p JOIN students AS s ON s.id = p.student_id; matches each payment to its student. JOIN on its own means INNER JOIN.

An inner join keeps only rows that have a match in both tables. Students without payments do not appear, and that is sometimes exactly what you want.

Short aliases (s, p) keep queries readable. Prefix columns with the alias when both tables have a column of the same name, such as id.

Step by step

  1. Join payments to students to show names.
  2. Add a filter for one class.
  3. Join a third table, such as classes, for the class teacher.
  4. Count how many rows each join returns and explain why.

Practice activity

Produce a payments list showing student name, class, amount and date.

Expected result: The list shows every payment with the right student details.

Check your understanding

  1. Which rows does an INNER JOIN keep?

    Show answer

    Only rows with a match in both tables.

  2. Why use table aliases?

    Show answer

    To keep queries short and to identify columns that appear in more than one table.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.