Joins & Aggregations
Combining and summarizing data from multiple tables.
Joins are used to combine rows from two or more tables based on a related column. Aggregations perform a calculation on a set of values and return a single value.
Visualizing Joins
Table A Table B
+---+-----+ +---+-----+
| id| val | | id| val |
+---+-----+ +---+-----+
| 1 | 'A' | | 1 | 'x' |
+---+-----+ +---+-----+
| 2 | 'B' | | 3 | 'z' |
+---+-----+ +---+-----+
INNER JOIN (Common values)
+---+-----+-----+
| 1 | 'A' | 'x' |
+---+-----+-----+
LEFT JOIN (All from A)
+---+-----+------+
| 1 | 'A' | 'x' |
+---+-----+------+
| 2 | 'B' | NULL |
+---+-----+------+
RIGHT JOIN (All from B)
+---+------+-----+
| 1 | 'A' | 'x' |
+---+------+-----+
| 3 | NULL | 'z' |
+---+------+-----+
FULL OUTER JOIN (All from both)
+---+------+------+
| 1 | 'A' | 'x' |
+---+------+------+
| 2 | 'B' | NULL |
+---+------+------+
| 3 | NULL | 'z' |
+---+------+------+
Example: LEFT JOIN with Aggregation (Explained)
-- Clause: SELECT
-- Purpose: Specifies the columns you want in the result.
-- Here, we get the author's name and calculate the number of posts.
SELECT
authors.name,
COUNT(posts.id) AS post_count
-- Clause: FROM
-- Purpose: The starting table for the query.
FROM authors
-- Clause: LEFT JOIN
-- Purpose: Combines rows from another table ('posts').
-- It keeps ALL rows from the 'authors' (left) table,
-- even if there's no match in 'posts'.
LEFT JOIN posts ON authors.id = posts.author_id
-- Clause: GROUP BY
-- Purpose: Used with aggregate functions (like COUNT) to group rows.
-- We group by author to count posts for each unique author.
GROUP BY authors.id;
Common Joins
`INNER JOIN`: Returns records that have matching values in both tables. `LEFT JOIN`: Returns all records from the left table, and matched records from the right table. `RIGHT JOIN`: The opposite of a LEFT JOIN. `FULL OUTER JOIN`: Returns all records when there is a match in either left or right table.
Aggregations & Group By
Aggregation functions (`COUNT`, `SUM`, `AVG`, etc.) summarize data. `GROUP BY` collapses multiple rows into a single summary row.
Visualizing Aggregation with GROUP BY
Original 'sales' table
+---------+--------+
| product | amount |
+---------+--------+
| 'Apple' | 10 |
| 'Orange'| 15 |
| 'Apple' | 20 |
| 'Grape' | 5 |
| 'Orange'| 10 |
+---------+--------+
Query: SELECT product, SUM(amount) FROM sales GROUP BY product;
Result
+---------+------------+
| product | SUM(amount)|
+---------+------------+
| 'Apple' | 30 |
| 'Orange'| 25 |
| 'Grape' | 5 |
+---------+------------+