Multi-table Queries

Multiple tables (implicit join)

You can query multiple tables by listing them in FROM and linking them in WHERE. This is the older syntax — explicit JOIN is preferred for clarity.

SELECT o.lahiosoite, p.paikkakunta
FROM lahiosoite o, paikkakunta p
WHERE o.postinumero = p.pnumero;

o and p are table aliases — short names that make the query easier to read.

JOINs

Joins combine rows from two or more tables based on a related column.

INNER JOIN

Returns only rows that have a match in both tables.

SELECT students.name, grades.grade
FROM students
INNER JOIN grades ON students.id = grades.student_id;

If a student has no grade, they are not included in the result.

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table. If there is no match, the right side columns are NULL.

SELECT students.name, grades.grade
FROM students
LEFT JOIN grades ON students.id = grades.student_id;

All students are included — those without a grade show NULL for grade.

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left table. If there is no match, the left side columns are NULL.

SELECT students.name, grades.grade
FROM students
RIGHT JOIN grades ON students.id = grades.student_id;

All grades are included — even if the student record is missing.

INFO

Some DBMS might not have RIGHT JOIN. Same result can be achieved by switching the table order in a LEFT JOIN query.

This RIGHT JOIN:

SELECT students.name, grades.grade
FROM students
RIGHT JOIN grades ON students.id = grades.student_id;

Can be rewritten as a LEFT JOIN by swapping the tables:

SELECT students.name, grades.grade
FROM grades
LEFT JOIN students ON students.id = grades.student_id;

Both queries return all grades, even if the student record is missing.

JOIN comparison

JOIN type	Left table rows	Right table rows
INNER JOIN	Only matched	Only matched
LEFT JOIN	All	Only matched
RIGHT JOIN	Only matched	All

Aliases

Table aliases shorten long table names and are required when joining a table to itself.

SELECT s.name, g.grade
FROM students s
INNER JOIN grades g ON s.id = g.student_id;

Joining multiple tables

You can chain multiple joins to connect more than two tables.

SELECT s.name, c.course_name, g.grade
FROM students s
INNER JOIN grades g ON s.id = g.student_id
INNER JOIN courses c ON g.course_id = c.id;

Self join

A table can be joined to itself. This requires aliases to distinguish the two copies.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Exercises

1. Write an INNER JOIN that combines two related tables.
2. Rewrite the same query using LEFT JOIN and compare the results.
3. Use RIGHT JOIN and explain which rows differ from LEFT JOIN.
4. Join three tables together in a single query.
5. Write a self join to find related rows within the same table.
6. Rewrite an implicit join (comma syntax) as an explicit JOIN.
7. Use a LEFT JOIN to find rows in the left table that have no match in the right table (hint: WHERE ... IS NULL).