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      |
+---------+------------+