JOINs & Aggregates

Code

-- Inner join with aggregate
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id
ORDER BY post_count DESC;

-- Filter groups with HAVING
SELECT user_id, AVG(published) AS publish_rate
FROM posts
GROUP BY user_id
HAVING publish_rate < 0.5;

-- Self join for hierarchies
CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT,
  parent_id INTEGER REFERENCES categories(id)
);

SELECT c.name AS child, p.name AS parent
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Explanation

SQLite supports INNER, LEFT, and self joins just like other SQL databases. GROUP BY collapses rows for aggregation, and HAVING filters groups after aggregation (unlike WHERE, which filters before). Self-joins with table aliases model hierarchical data such as category trees.

More SQLite Snippets