Useful SQL Snippets — SQL Snippets Library

What is the SQL Snippets Library?

The SQL Snippets Library is a free collection of reusable SQL code templates and recipes that you can copy into your own queries with a single click. Instead of rewriting the same boilerplate every time you need a recursive CTE, a running total, or a paginated result set, you can grab a tested snippet, adjust a table or column name, and move on. The library is organized into categories — CTE & Recursive, Window Functions, Pivot / Unpivot, Pagination, String Aggregation, Date & Time, Performance, MERGE / UPSERT, and Utility — so you can jump straight to the pattern you need. Most snippets are written in standard SQL and include dialect-specific variants (for example STRING_AGG for PostgreSQL versus GROUP_CONCAT for MySQL) so they work across PostgreSQL, MySQL, SQL Server, SQLite, and Oracle with minimal changes. Every snippet includes a short description, dialect tags, and ready-to-run SQL, and the whole library runs in your browser — nothing you view or copy is ever sent to a server.

How to Use the Snippets Library

1. Open the Snippets page and scroll through all snippets, or use the category tabs at the top to filter the list.
2. Click a category such as Window Functions, Pagination, or Performance to narrow down to a specific pattern.
3. Read each snippet's title and description to confirm it matches what you are trying to do.
4. Click the Copy button on the snippet card to copy the SQL to your clipboard instantly.
5. Paste the snippet into your editor or query tool, then rename tables, columns, and values to fit your own schema.

Common Use Cases

• Traversing hierarchical data such as employee org charts or category trees with a recursive CTE.
• Deduplicating rows, computing running totals, or ranking results per group with window functions.
• Adding OFFSET/FETCH or ROW_NUMBER-based pagination to list and search queries.
• Building cross-tab reports by pivoting rows into columns.
• Inspecting slow queries with EXPLAIN patterns and missing-index checks.
• Upserting data with MERGE / ON CONFLICT recipes instead of hand-rolling conditional inserts.

Example: Running Total with a Window Function

This snippet computes a cumulative sum of amount over time. Because the window function orders by order_date, each row's running_total is the sum of every amount up to and including that date — no self-join or subquery required, and the same pattern can be reused for any cumulative metric.

Tips for Getting the Most Out of the Library

Always check the dialect tags on a snippet before pasting it into a production query — several recipes ship PostgreSQL and MySQL variants of the same pattern, and a function like LISTAGG only exists in Oracle. Treat snippets as starting points rather than finished code: rename aliases to match your conventions, add your own WHERE filters, and double-check that window-function framing behaves the way you expect before running anything against a large table. Finally, bookmark the categories you use most often so you can jump straight to them next time.

Useful SQL Snippets Every Developer Should Bookmark

If you only ever save a handful of useful SQL snippets, these are the patterns that pay off week after week. All of them are in the library below, and most map to a tool on this site so you can format, diff, or inspect the result without leaving the browser:

• Running total with a window function — SUM(amount) OVER (ORDER BY order_date) replaces a self-join for cumulative metrics such as revenue-to-date. See the window function guide.
• Deduplicate rows with ROW_NUMBER — keep the newest row per customer using ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and filter rn = 1. Run it through the SQL formatter first to catch a missing alias.
• Recursive CTE for hierarchies — employee org charts, category trees, and bills of materials all traverse the same way: an anchor row plus a recursive term joined on the parent key. See the recursive CTE guide.
• Keyset pagination instead of OFFSET — WHERE (created_at, id) > (?, ?) ORDER BY created_at, id LIMIT 50 stays fast on large tables where OFFSET 100000 collapses.
• CASE-based pivot — turns a long metrics table into a wide report without vendor-specific PIVOT syntax, so the same snippet runs on PostgreSQL, MySQL, and SQLite. Adapt it with the dialect converter.
• String aggregation — STRING_AGG (PostgreSQL), GROUP_CONCAT (MySQL), and LISTAGG (Oracle) do the same job under three different names.
• Find what is blocking your database — the running-query and blocking-session snippets surface long transactions before they become an incident. Check the plan with the EXPLAIN visualizer.
• Upsert with ON CONFLICT / MERGE — one statement instead of a check-then-insert race condition.

Inside the SQL Code Snippet Library

Every snippet in this SQL code snippet library carries a title, a one-line description, dialect tags, and copy-ready SQL. The 34 snippets are grouped into nine categories so you can scan the list instead of scrolling a wall of code:

• CTE & Recursive (3) — employee hierarchies, generated date series, category trees built from adjacency lists.
• Window Functions (6) — ROW_NUMBER, RANK / DENSE_RANK, LAG / LEAD, running totals, FIRST_VALUE / LAST_VALUE, NTILE bucketing.
• Pivot / Unpivot (3) — CASE-based pivots, native SQL Server / Oracle PIVOT, and columns-to-rows unpivot.
• Pagination (3) — OFFSET / FETCH, keyset (seek) pagination, and MySQL LIMIT / OFFSET.
• String Aggregation (3) — GROUP_CONCAT, STRING_AGG, and LISTAGG side by side.
• Date & Time (4) — date truncation by month / week / year, precise age calculation, overlapping range detection, generated date series.
• Performance (4) — missing-index queries, running and blocking sessions, and EXPLAIN-driven diagnostics.
• MERGE / UPSERT (3) — PostgreSQL ON CONFLICT, SQL Server MERGE, and conditional update-else-insert recipes.
• Utility (5) — table sizes, duplicate detection, safe column renames, and other maintenance one-liners.

The category tabs at the top filter the grid instantly, and the copy button puts the whole snippet on your clipboard — no signup and no server round trip. If a snippet needs adjusting, paste it into the SQL formatter, then use SQL diff to review exactly what you changed before it goes anywhere near production.

SQL Developer Snippets: Building Your Own Reusable Library

Team snippet libraries always drift, because every SQL developer snippets differently: one person keeps them in a notes file, another in bookmarks, a third retypes them from memory each time. A little structure makes reuse actually stick.

• Name snippets by intent, not syntax. "Latest row per customer" survives a decade of refactors; "row_number_2" does not.
• Parameterize, do not hardcode. Use placeholders such as {{table}}, {{date_column}}, or :param so a snippet cannot be aimed at the wrong table by accident.
• Keep them in version control. A snippets repository in Git gives you history, review, and one source of truth for the team.
• Store them in your editor as well. VS Code, SSMS, and DBeaver all support user-defined snippets — see our guides for VS Code, SSMS, and DBeaver.
• Format on paste. Snippets copied between tools lose their indentation fast; running the SQL beautifier or pretty printer before you commit keeps the library readable.
• Record the dialect and the version. Whether QUALIFY, MERGE, or ON CONFLICT is available depends on the engine and its version — note it on the snippet card.

Frequently Asked Questions

What SQL snippets are available in the library?

The library covers nine categories and 34 snippets: Recursive CTEs (hierarchies, date ranges), Window Functions (ROW_NUMBER, RANK, LAG/LEAD, running totals), Pagination (OFFSET/FETCH, keyset), Pivot Tables (CASE-based and native PIVOT), String Aggregation (GROUP_CONCAT, STRING_AGG, LISTAGG), Date & Time queries, Performance analysis (missing indexes, blocking queries), MERGE/UPSERT patterns, and utility snippets.

Is the SQL snippets library free to use?

Yes, completely free. Every snippet is available without an account, signup, or payment, and each one includes a description plus ready-to-run SQL that copies with a single click. There is no limit on how many snippets you view or copy.

Which SQL dialects do the snippets support?

Snippets are written in standard SQL with dialect-specific variants where the syntax differs — STRING_AGG for PostgreSQL, GROUP_CONCAT for MySQL, LISTAGG for Oracle. Dialect tags on each card tell you what it targets, and most snippets run on PostgreSQL, MySQL, SQL Server, SQLite, and Oracle with minor edits.

How do I copy a snippet into my own query?

Click the Copy button on any snippet card and the full SQL lands on your clipboard. Paste it into your editor or query console, then rename the tables, columns, and placeholders to match your schema. If the indentation suffers in the round trip, run it through the SQL formatter before executing.

Are these SQL snippets safe to run on a production database?

Treat every snippet as a starting point. Read it first, confirm the table and column names, add a WHERE clause while you test, and inspect the query plan with the EXPLAIN visualizer before running anything against a large table. Nothing you view or copy is sent to a server — the whole library runs client-side in your browser.

Can I contribute or request new SQL snippets?

The library is maintained by the SQLFormat.io team and expanded regularly. If a pattern you use daily is missing, tell us — new snippets ship through site updates, and the tool itself runs entirely client-side with no server storage.

Can I use these SQL snippets as query templates for my own tables?

Yes - every snippet is meant as a template, not a finished query. Copy it, then rename the tables, columns, and placeholders to match your schema; the snippets deliberately use obvious names such as orders, customer_id, and {{table}} so nothing gets silently aimed at the wrong object. Keep the renamed version in your own snippet library or in Git so the next query starts from a working template instead of a blank editor.