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
- 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.
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 studentsnames the two tables to combine.ON students.id = enrollments.student_idis the matching rule. For each enrollment, the database finds the student whoseidequals that row'sstudent_idand 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.
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.
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.
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;
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.
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.
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
Join enrollments to courses. Show the course title and the score for every enrollment that has a score.
-- Show each course title with the score of every enrollment in it, -- but only for enrollments that HAVE a score. -- Columns: title, score
What does an INNER JOIN return?
Summary
JOIN ... ONcombines rows from two tables in one query.- The
ONcondition usually matches a primary key with a foreign key. - Table aliases such as
AS ekeep 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.