Velamuvi AIAcademy

Lesson 11 of 17 · Combining Tables

INNER JOIN

Combine two tables with SQL INNER JOIN. Learn the ON condition, table aliases and how to filter and sort joined data, with runnable examples.

  • 8 min read
  • Beginner
  • Reviewed
In this lesson you will learn
  • How to combine rows from two tables in one query
  • How the ON condition matches rows
  • How to shorten queries with table aliases
  • Which rows an inner join leaves out

One query instead of two

In the last lesson you followed a foreign key by hand: read student_id from enrollments, then look the student up in students. A JOIN asks the database to do that matching for you.

Try it yourselfSQL
SELECT students.name, enrollments.course_id, enrollments.score
FROM enrollments
JOIN students ON students.id = enrollments.student_id;

Every enrollment now shows the student's name instead of a number.

How a join works

Read the query in two parts.

  • FROM enrollments JOIN students names the two tables to combine.
  • ON students.id = enrollments.student_id is the matching rule. For each enrollment, the database finds the student whose id equals that row's student_id and glues the two rows together.

The matching rule is almost always the same shape: primary key of one table = foreign key of the other.

Because both tables are now in play, column names are written as table.column. That removes any doubt about which table a column belongs to, and it is required when both tables have a column with the same name, such as id.

Table aliases

Writing full table names again and again is tiring. Give each table a short alias with AS.

Try it yourselfSQL
SELECT s.name, e.course_id, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id;

This is the same query. Single-letter aliases are the normal style for joins.

The same join, the other way round

Now replace the course number with the course title, by joining to courses instead.

Try it yourselfSQL
SELECT c.title, e.student_id, e.progress
FROM enrollments AS e
JOIN courses AS c ON c.id = e.course_id;

Everything you know still works

A joined result behaves like a single wide table. You can filter it, sort it and limit it, using columns from either table.

Try it yourselfSQL
SELECT s.name, s.country, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
WHERE e.score >= 90
ORDER BY e.score DESC;
Try it yourselfSQL
SELECT s.name, e.course_id, e.progress
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
WHERE s.country = 'India'
ORDER BY s.name, e.course_id;

The second query filters on a column from students and shows columns from enrollments. Neither table could answer that question alone.

Grouping a join

Aggregates work too. This counts enrollments per country, something that needs the country from one table and the enrollments from the other.

Try it yourselfSQL
SELECT s.country, COUNT(*) AS enrollments
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
GROUP BY s.country
ORDER BY enrollments DESC, s.country;

What an inner join leaves out

The plain JOIN is an inner join. It returns only rows that have a match in both tables.

Try it yourselfSQL
SELECT COUNT(*) AS students_in_table
FROM students;

SELECT COUNT(DISTINCT s.id) AS students_in_join
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id;

There are twelve students, but only ten appear in the join. Two students have not enrolled in anything, so no enrollment row matches them, and they are left out.

Sometimes that is what you want. Sometimes it hides exactly the rows you care about. The next lesson shows how to keep them.

Common mistakes

Practice exercise

Join enrollments to courses. Show the course title and the score for every enrollment that has a score.

Your solutionSQL
-- Show each course title with the score of every enrollment in it,
-- but only for enrollments that HAVE a score.
-- Columns: title, score
Knowledge check

What does an INNER JOIN return?

Summary

  • JOIN ... ON combines rows from two tables in one query.
  • The ON condition usually matches a primary key with a foreign key.
  • Table aliases such as AS e keep queries short.
  • A joined result can be filtered, sorted, grouped and limited like any table.
  • An inner join returns only rows with a match on both sides.

Next: keeping the rows that have no match.

Finished reading? Tick it off to track your progress.