Several tables and the self join

Many questions need three or four tables, not two. And sometimes a table has to be joined with itself.

Joins in a chain

Each JOIN adds one more table to the result. Student → group → faculty:

SELECT s.first_name, s.last_name, g.name AS group_name, f.name AS faculty
FROM students s
JOIN study_groups g ON g.id = s.group_id
JOIN faculties f ON f.id = g.faculty_id
LIMIT 8;

Through a link table (M:N) — student, subject and score:

SELECT s.first_name, sub.name AS subject, gr.score
FROM grades gr
JOIN students s ON s.id = gr.student_id
JOIN subjects sub ON sub.id = gr.subject_id
ORDER BY gr.score DESC
LIMIT 8;

Every JOIN gets its own ON. Build the chain from the schema: each next table must connect to one already in the query.

Self join — a table joined with itself

In the company database an employee’s manager lives in the same employees table (manager_id). To see employees next to their managers, take the table twice under two aliases:

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
JOIN employees m ON m.id = e.manager_id
LIMIT 8;

e plays the employee, m plays the manager. Without aliases this query cannot be written.

A calculation across tables

Four tables: order lines → orders → products → categories. Revenue per category from delivered orders:

SELECT c.name AS category, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
JOIN categories c ON c.id = p.category_id
WHERE o.status = 'delivered'
GROUP BY c.name
ORDER BY revenue DESC;

Remember

• Every extra table is another JOIN … ON ….
• Self join: one table, two different aliases.
• Find the “path” in the schema before writing the query.