Relational data is often split across several tables. Joins recombine related rows, while aggregation summarizes groups of rows.

Inner joins

Suppose posts.author_id refers to users.id. An inner join can combine each post with its author:

SELECT posts.title, users.name
FROM posts
JOIN users ON users.id = posts.author_id;

Only row combinations satisfying the join condition appear.

Left joins

A left join keeps every row from the left relation even when no matching row exists on the right:

SELECT users.name, posts.title
FROM users
LEFT JOIN posts ON posts.author_id = users.id;

Missing right-side values are represented with NULL.

Aggregates

Functions such as COUNT, SUM, AVG, MIN and MAX summarize several rows.

SELECT author_id, COUNT(*)
FROM posts
GROUP BY author_id;

GROUP BY partitions rows into groups before the aggregate is calculated.

WHERE and HAVING

WHERE filters individual rows before grouping. HAVING filters the groups produced by aggregation.

Joins and aggregation are both relational operations: joins combine related tuples, while aggregates intentionally collapse many tuples into summary values.