SQL Formatter
Format and minify SQL for PostgreSQL, MySQL, SQLite and more.
A practical library of SQL patterns you can copy and adapt, each with a short explanation.
Runs in your browser. Your input is never uploaded.
Search or filter by topic.
Read the explanation.
Copy the query or open it in the formatter.
Output
SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id ORDER BY 2 DESC LIMIT 10;Format and minify SQL for PostgreSQL, MySQL, SQLite and more.
Format, beautify and minify JSON with custom indentation.
Convert CSV text into a JSON array of objects.
Standard SQL that works on PostgreSQL, MySQL and SQLite unless noted.
No, they are reference snippets.
Always test on a copy of your data before running UPDATE or DELETE.
INNER, LEFT, RIGHT, FULL and CROSS joins explained with a small example dataset, result tables and common pitfalls.
customers(id, name, email, country, created_at) orders(id, customer_id, total, status, created_at) order_items(id, order_id, product_id, quantity, price) products(id, name, category, price)
26 examples
Basics
Return only the columns you need instead of SELECT *.
SELECT id, name, email
FROM customers
ORDER BY name;Basics
Combine conditions with AND / OR and use parentheses to be explicit.
SELECT id, total
FROM orders
WHERE status = 'paid'
AND total >= 100
AND created_at >= '2024-01-01';Basics
% matches any sequence of characters, _ matches exactly one.
SELECT name, email
FROM customers
WHERE email LIKE '%@example.com';Basics
Always add a deterministic ORDER BY when paginating.
SELECT id, name
FROM products
ORDER BY id
LIMIT 20 OFFSET 40;Basics
Concise alternatives to chains of OR / range comparisons.
SELECT *
FROM orders
WHERE status IN ('paid', 'shipped')
AND total BETWEEN 50 AND 500;Joins
Only rows that have a match in both tables.
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id;Joins
Keep every left row; NULL on the right means no match.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Joins
Chain joins to follow foreign keys.
SELECT c.name, p.name AS product, oi.quantity
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;Joins
Join a table to itself, e.g. customers from the same country.
SELECT a.name AS customer_a, b.name AS customer_b, a.country
FROM customers a
JOIN customers b ON a.country = b.country AND a.id < b.id;Aggregation
GROUP BY collapses rows into one per group.
SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
ORDER BY customers DESC;Aggregation
Aggregate then sort by the aggregate.
SELECT customer_id, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY revenue DESC
LIMIT 10;Aggregation
WHERE filters rows before grouping; HAVING filters after.
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 5;Aggregation
Pivot-style counts using CASE inside an aggregate.
SELECT
COUNT(*) AS total,
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded
FROM orders;Subqueries & CTEs
Customers whose spend is above the average order.
SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);Subqueries & CTEs
Check for related rows without joining duplicates.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 500
);Subqueries & CTEs
Name an intermediate result to keep queries readable.
WITH monthly AS (
SELECT customer_id, SUM(total) AS spent
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id
)
SELECT c.name, m.spent
FROM monthly m
JOIN customers c ON c.id = m.customer_id
ORDER BY m.spent DESC;Window functions
Latest order per customer: rank, then keep rank 1.
SELECT *
FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
FROM orders o
) t
WHERE rn = 1;Window functions
Cumulative sum ordered by date.
SELECT created_at, total,
SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;Window functions
RANK leaves gaps after ties; DENSE_RANK does not.
SELECT name, price,
RANK() OVER (ORDER BY price DESC) AS price_rank
FROM products;Modifying data
List columns explicitly so schema changes don’t break inserts.
INSERT INTO customers (name, email, country)
VALUES ('Ada Lovelace', 'ada@example.com', 'GB'),
('Grace Hopper', 'grace@example.com', 'US');Modifying data
Always include WHERE; test with a SELECT first.
UPDATE orders
SET status = 'cancelled'
WHERE id = 1042;Modifying data
Run the equivalent SELECT first and use a transaction.
BEGIN;
DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01';
-- check the affected row count, then:
COMMIT;Schema
Primary key, foreign key, NOT NULL and defaults.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);Schema
Index columns used in WHERE / JOIN to speed up lookups.
CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at);Dates
Use date functions to bucket rows (PostgreSQL syntax shown).
SELECT date_trunc('month', created_at) AS month, SUM(total) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;Dates
Find values that appear more than once.
SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;