JOIN: combining tables

If we look at the enrollments table, we see numbers like “student 1, course 6, score 88”. But people want “Aysel — Python Basics — 88”. The name lives in students, the course title in courses and the score in enrollments. To see them together we need to join the tables with JOIN.

INNER JOIN

In FROM A JOIN B ON condition, the ON part says how to pair the rows of the two tables. Usually it is a foreign key equal to a primary key: e.student_id = s.id. Every pair of rows that satisfies ON becomes one row of the result. INNER JOIN keeps only rows that have a match in both tables.

SELECT s.first_name, e.course_id, e.score
FROM students AS s
INNER JOIN enrollments AS e ON e.student_id = s.id
WHERE s.city = 'Bakı'
ORDER BY s.id, e.course_id;

first_name | course_id | score
Aysel | 1 | 92
Aysel | 6 | 88
Leyla | 2 | 95
Leyla | 7 | 90
Rəşad | 3 | 77
Rəşad | 6 | 99
Kamran | 6 | 91

Several JOINs can follow one another. Below we join three tables: we add the student's name and the course title to each enrollment and keep only the maths courses.

SELECT s.first_name, c.title, e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id
WHERE c.subject = 'Math'
ORDER BY c.title, e.score DESC;

first_name | title | score
Fidan | Algebra | 97
Aysel | Algebra | 92
Nigar | Algebra | 85
Murad | Algebra | 75
Leyla | Geometry | 95
Səbinə | Geometry | 79
Orxan | Geometry | 70

LEFT JOIN: keeping rows without a match

The Monitor has never been ordered. INNER JOIN would simply drop it from the result. LEFT JOIN keeps every row of the left table (the one after FROM); when there is no match in the right table, its columns are filled with NULL.

SELECT p.name, o.id AS order_id, o.quantity
FROM products AS p
LEFT JOIN orders AS o ON o.product_id = p.id
WHERE p.category = 'Electronics'
ORDER BY p.id, o.id;

name | order_id | quantity
Laptop | 1 | 1
Smartphone | 4 | 1
Smartphone | 7 | 1
Headphones | 2 | 2
Headphones | 11 | 1
Monitor | NULL | NULL

This enables a very useful trick: finding rows without a match. If the right table's primary key is NULL after a LEFT JOIN, no match was found. Questions like “products nobody has bought” or “students not enrolled in any course” are answered this way.

SELECT p.name
FROM products AS p
LEFT JOIN orders AS o ON o.product_id = p.id
WHERE o.id IS NULL;

name
Monitor

JOIN and GROUP BY together

A joined result can be grouped as well. Let's return to the question from the last lesson, now with customer names and the money they spent: we join each order to its customer and product, multiply quantity by price and add it up per customer.

SELECT c.name,
       COUNT(o.id) AS orders,
       ROUND(SUM(o.quantity * p.price), 2) AS total_spent
FROM customers AS c
JOIN orders   AS o ON o.customer_id = c.id
JOIN products AS p ON p.id = o.product_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

name | orders | total_spent
Anar Mustafayev | 3 | 1723
Emre Yılmaz | 2 | 941.99
Zəhra Hüseynli | 2 | 935.99
Lalə Əhmədova | 2 | 184.5
John Carter | 1 | 69.98
Olga Ivanova | 1 | 55
Mehmet Kaya | 1 | 31.6

Be careful when counting after a LEFT JOIN: COUNT(*) would give 1 for the Monitor, because a row full of NULLs is still a row. COUNT(o.id) counts only real orders and returns 0. COALESCE(SUM(...), 0) turns an empty sum into zero.

SELECT p.name,
       COUNT(o.id)                 AS times_ordered,
       COALESCE(SUM(o.quantity), 0) AS units
FROM products AS p
LEFT JOIN orders AS o ON o.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units DESC, p.name;

name | times_ordered | units
Notebook | 2 | 30
Pen set | 1 | 4
Headphones | 2 | 3
Water bottle | 1 | 3
Desk lamp | 1 | 2
Smartphone | 2 | 2
Backpack | 1 | 1
Chess set | 1 | 1
Laptop | 1 | 1
Monitor | 0 | 0

Other kinds of joins

SELECT COUNT(*) AS pairs
FROM students, courses;

pairs
84

SELECT o.id, o.customer_id, o.product_id, o.quantity
FROM orders AS o
WHERE o.quantity >= 2
ORDER BY o.id;

id | customer | product | quantity
2 | Anar Mustafayev | Headphones | 2
3 | Lalə Əhmədova | Notebook | 20
6 | John Carter | Desk lamp | 2
8 | Zəhra Hüseynli | Water bottle | 3
10 | Mehmet Kaya | Pen set | 4
12 | Anar Mustafayev | Notebook | 10

SELECT p.name, SUM(o.quantity) AS units
FROM products AS p
JOIN orders AS o ON o.product_id = p.id
WHERE p.category = 'Electronics'
GROUP BY p.id, p.name
ORDER BY units DESC, p.name;

name | units
Headphones | 3
Smartphone | 2
Laptop | 1
Monitor | 0