INNER JOIN

JOIN combines two tables by their keys. It is one of the most powerful and most used features of SQL.

The pattern

SELECT …
FROM table_a
JOIN table_b ON table_a.key = table_b.key;

After ON comes the condition saying which rows belong together. Usually it is “foreign key = primary key”.

Your first JOIN

The group name next to the faculty name:

SELECT study_groups.name, faculties.name
FROM study_groups
JOIN faculties ON study_groups.faculty_id = faculties.id;

Both tables have a name column, so you must say whose it is: study_groups.name.

Aliases — shorter to write

SELECT g.name AS group_name, f.name AS faculty
FROM study_groups g
JOIN faculties f ON g.faculty_id = f.id
ORDER BY f.name, g.name;

study_groups g gives the table the short name g. In queries with several tables this is very convenient.

JOIN + WHERE + GROUP BY

After a JOIN everything works as usual: filtering, grouping, sorting.

SELECT f.name AS faculty, COUNT(*) AS teachers
FROM teachers t
JOIN faculties f ON f.id = t.faculty_id
GROUP BY f.name
ORDER BY teachers DESC;

What if you forget ON?

Without a condition, every row pairs up with every row of the other table. 32 groups × 6 faculties = 192 meaningless rows. This is called a Cartesian product, and on big tables it can bring a database to its knees. If you suddenly get far too many rows, check your ON first.

What INNER JOIN really is

JOIN = INNER JOIN. It returns only the rows that have a match in both tables. A group without a curator, for example, disappears when joined with teachers. If you also need the rows without a match, you need LEFT JOIN — the next lesson.

Remember

• JOIN … ON a.fk = b.id links two tables by a key.
• For columns with the same name, write the table name or alias.
• Far too many rows means a missing ON condition.