Home › Templates › SQL snippets

🗄️ SQL snippets cheat sheet: duplicates, joins, pagination and indexes

A lot of SQL work is a small set of patterns repeated with different table names. These queries cover the ones developers and analysts reach for most.

Replace the [brackets] with your tables and columns. Always run an UPDATE or DELETE as a SELECT first. Save the pack to CopyCat to paste them quickly.

Add this pack to CopyCat All templates

Find duplicates

SELECT [column], COUNT(*) AS n FROM [table] GROUP BY [column] HAVING COUNT(*) > 1 ORDER BY n DESC;

Count by group

SELECT [category], COUNT(*) AS total FROM [table] GROUP BY [category] ORDER BY total DESC;

Left join with missing rows

SELECT a.* FROM [table_a] a LEFT JOIN [table_b] b ON b.[a_id] = a.id WHERE b.id IS NULL;

Paginate results

SELECT * FROM [table] ORDER BY [id] LIMIT [20] OFFSET [40];

Latest row per group

SELECT DISTINCT ON ([group_column]) * FROM [table] ORDER BY [group_column], [created_at] DESC;

Safe update (preview first)

SELECT * FROM [table] WHERE [condition]; BEGIN; UPDATE [table] SET [column] = [value] WHERE [condition]; -- check the result, then COMMIT; or ROLLBACK;

Add an index

CREATE INDEX CONCURRENTLY idx_[table]_[column] ON [table] ([column]);

Explain a slow query

EXPLAIN (ANALYZE, BUFFERS) [your query];

Table sizes (Postgres)

SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 15;

Dump and restore (Postgres)

pg_dump -Fc -h [host] -U [user] [database] > [backup.dump] pg_restore -d [database] [backup.dump]

Stop retyping these

Add the whole "SQL snippets" pack to CopyCat in one click and edit anything you like.

Add this pack