$devvkit resources --cheatsheet sql-cheatsheet
SQL Cheatsheet
[databases]
Every SQL command and clause you need: from SELECT to window functions, joins, aggregation, DDL, and transactions, with syntax and real use cases.113 commands · 8 sections
SQL is the language of relational databases. This cheatsheet covers the commands you write daily: querying with SELECT, filtering and sorting, every JOIN type, aggregation and GROUP BY, window functions, modifying data with INSERT/UPDATE/DELETE, and schema management with DDL.
Each entry shows real syntax followed by the use case. Most examples work in PostgreSQL, MySQL, SQLite, and SQL Server with minor dialect differences.
Querying Data13
$
SELECT * FROM users;Use case: Return every column and row from a table: the simplest read.Example: SELECT * FROM users;
$
SELECT id, name, email FROM users;Use case: Return only specific columns: less data, faster queries.Example: SELECT id, name, email FROM users;
$
SELECT DISTINCT country FROM users;Use case: Return unique values: which countries have users?Example: SELECT DISTINCT country FROM users;
$
SELECT name AS full_name FROM users;Use case: Rename a column in the result: cleaner output.Example: SELECT name AS full_name FROM users;
$
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;Use case: Combine columns into one (PostgreSQL/MySQL: || or CONCAT).Example: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
$
SELECT 10 + 5 AS result;Use case: Run calculations without a table: test expressions.Example: SELECT 10 + 5 AS result;
$
SELECT COALESCE(email, phone) FROM users;Use case: Return the first non-NULL value: fallback columns.Example: SELECT COALESCE(email, phone) FROM users;
$
SELECT NULLIF(a, b);Use case: Return NULL when two values are equal: avoid division by zero.Example: SELECT total / NULLIF(orders, 0) AS avg FROM stats;
$
SELECT COUNT(*) FROM users;Use case: Count all rows: how many users exist?Example: SELECT COUNT(*) FROM users;
$
SELECT COUNT(DISTINCT country) FROM users;Use case: Count unique values: how many countries are represented?Example: SELECT COUNT(DISTINCT country) FROM users;
$
SELECT * FROM users LIMIT 10;Use case: Return only the first 10 rows: preview a large table.Example: SELECT * FROM users LIMIT 10;
$
SELECT * FROM users LIMIT 10 OFFSET 20;Use case: Skip 20 rows, return 10: page 3 of results.Example: SELECT * FROM users LIMIT 10 OFFSET 20;
$
SELECT * FROM users ORDER BY created_at DESC LIMIT 1;Use case: The latest user: most recent row first.Example: SELECT * FROM users ORDER BY created_at DESC LIMIT 1;
Filtering & Sorting12
$
SELECT * FROM users WHERE status = 'active';Use case: Filter rows with a condition.Example: SELECT * FROM users WHERE status = 'active';
$
SELECT * FROM orders WHERE total > 100 AND status = 'paid';Use case: Combine conditions with AND: both must be true.Example: SELECT * FROM orders WHERE total > 100 AND status = 'paid';
$
SELECT * FROM users WHERE plan = 'free' OR plan = 'pro';Use case: Either condition true: plan is one of these.Example: SELECT * FROM users WHERE plan = 'free' OR plan = 'pro';
$
SELECT * FROM users WHERE plan IN ('free', 'pro', 'team');Use case: Cleaner than multiple ORs: value in a list.Example: SELECT * FROM users WHERE plan IN ('free', 'pro', 'team');
$
SELECT * FROM users WHERE email LIKE '%@gmail.com';Use case: Pattern match: % is a wildcard. Find Gmail users.Example: SELECT * FROM users WHERE email LIKE '%@gmail.com';
$
SELECT * FROM users WHERE name ILIKE 'jane%';Use case: Case-insensitive pattern match (PostgreSQL): find Jane, jane, JANE.Example: SELECT * FROM users WHERE name ILIKE 'jane%';
$
SELECT * FROM users WHERE email IS NULL;Use case: NULL comparison: = NULL never works, always use IS NULL.Example: SELECT * FROM users WHERE email IS NULL;
$
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';Use case: Range filter: inclusive on both ends.Example: SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
$
SELECT * FROM orders WHERE total NOT BETWEEN 10 AND 100;Use case: Exclude a range: orders outside 10-100.Example: SELECT * FROM orders WHERE total NOT BETWEEN 10 AND 100;
$
SELECT * FROM users ORDER BY created_at DESC;Use case: Sort newest first.Example: SELECT * FROM users ORDER BY created_at DESC;
$
SELECT * FROM users ORDER BY country ASC, name DESC;Use case: Sort by multiple columns: country first, then name within country.Example: SELECT * FROM users ORDER BY country ASC, name DESC;
$
SELECT * FROM orders ORDER BY total DESC LIMIT 5;Use case: Top 5 orders by value: your best customers' biggest orders.Example: SELECT * FROM orders ORDER BY total DESC LIMIT 5;
Joins12
$
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;Use case: INNER JOIN: only rows matching in both tables.Example: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
$
SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;Use case: Alias tables and pick columns: the everyday join.Example: SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;
$
SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id;Use case: All customers, even those with zero orders: NULLs fill the missing side.Example: SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id;
$
SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL;Use case: Customers who NEVER ordered: the anti-join pattern.Example: SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL;
$
SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;Use case: All customers (right table) with matching orders.Example: SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;
$
SELECT * FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id;Use case: Everything from both tables: matched or not (not in MySQL).Example: SELECT * FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id;
$
SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id;Use case: Self-join: employees with their managers, one table joined to itself.Example: SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id;
$
SELECT * FROM a CROSS JOIN b;Use case: Cartesian product: every row of a with every row of b.Example: SELECT * FROM sizes CROSS JOIN colors;
$
SELECT * FROM orders JOIN customers USING (customer_id);Use case: Join when both tables share the same column name: less boilerplate.Example: SELECT * FROM orders JOIN customers USING (customer_id);
$
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id AND customers.country = 'US';Use case: Join with extra conditions: filter inside the join.Example: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id AND customers.country = 'US';
$
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id;Use case: Three-table join: orders with customer AND product details.Example: SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id;
$
SELECT * FROM orders JOIN LATERAL (SELECT MAX(price) AS max_price FROM order_items WHERE order_id = orders.id) x ON true;Use case: Lateral join: reference earlier tables inside a subquery (PostgreSQL).Example: SELECT * FROM orders JOIN LATERAL (SELECT MAX(price) FROM order_items WHERE order_id = orders.id) x ON true;
Aggregation12
$
SELECT country, COUNT(*) FROM users GROUP BY country;Use case: Count users per country: the fundamental GROUP BY.Example: SELECT country, COUNT(*) FROM users GROUP BY country;
$
SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 100;Use case: Filter groups after aggregation: countries with 100+ users.Example: SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 100;
$
SELECT department, AVG(salary) FROM employees GROUP BY department;Use case: Average salary per department.Example: SELECT department, AVG(salary) FROM employees GROUP BY department;
$
SELECT department, SUM(salary) FROM employees GROUP BY department;Use case: Total payroll per department.Example: SELECT department, SUM(salary) FROM employees GROUP BY department;
$
SELECT department, MIN(salary), MAX(salary) FROM employees GROUP BY department;Use case: Salary range per department.Example: SELECT department, MIN(salary), MAX(salary) FROM employees GROUP BY department;
$
SELECT COUNT(*), SUM(total) FROM orders;Use case: Multiple aggregates in one pass: order count and revenue.Example: SELECT COUNT(*), SUM(total) FROM orders;
$
SELECT year, month, SUM(revenue) FROM sales GROUP BY year, month ORDER BY year, month;Use case: Group by multiple columns: monthly revenue over years.Example: SELECT year, month, SUM(revenue) FROM sales GROUP BY year, month ORDER BY year, month;
$
SELECT GROUP_CONCAT(name) FROM users WHERE plan = 'pro';Use case: Concatenate values into one string (MySQL: GROUP_CONCAT, PostgreSQL: string_agg).Example: SELECT GROUP_CONCAT(name) FROM users WHERE plan = 'pro';
$
SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;Use case: Comma-joined list, sorted: tag lists and CSV building (PostgreSQL).Example: SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;
$
SELECT ARRAY_AGG(name) FROM users;Use case: Collect values into an array (PostgreSQL): pass to client code.Example: SELECT ARRAY_AGG(name) FROM users;
$
SELECT JSON_AGG(name) FROM users;Use case: Aggregate into a JSON array: build API payloads in SQL.Example: SELECT JSON_AGG(name) FROM users;
$
SELECT department, COUNT(*) FILTER (WHERE salary > 100000) FROM employees GROUP BY department;Use case: Conditional count per group (PostgreSQL): senior staff per department.Example: SELECT department, COUNT(*) FILTER (WHERE salary > 100000) FROM employees GROUP BY department;
Window Functions10
$
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;Use case: Rank employees by salary: ties share a rank with gaps.Example: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;
$
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;Use case: Rank without gaps: 1,2,2,3 instead of 1,2,2,4.Example: SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;
$
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;Use case: Unique sequential number per row: no ties.Example: SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
$
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;Use case: Rank within each department separately: per-group leaderboards.Example: SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
$
SELECT order_date, total, SUM(total) OVER (ORDER BY order_date) AS running_total FROM orders;Use case: Cumulative running total over time.Example: SELECT order_date, total, SUM(total) OVER (ORDER BY order_date) AS running_total FROM orders;
$
SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3 FROM monthly;Use case: 3-month moving average: smooth out seasonality.Example: SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3 FROM monthly;
$
SELECT name, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM employees;Use case: Compare each row to the previous: month-over-month changes.Example: SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS mom FROM monthly;
$
SELECT month, revenue, revenue - LEAD(revenue) OVER (ORDER BY month) AS next_diff FROM monthly;Use case: Compare to the NEXT row: peek ahead.Example: SELECT month, revenue, revenue - LEAD(revenue) OVER (ORDER BY month) AS next_diff FROM monthly;
$
SELECT month, revenue, FIRST_VALUE(revenue) OVER (ORDER BY month) FROM monthly;Use case: First value in the window: compare each month to January.Example: SELECT month, revenue, FIRST_VALUE(revenue) OVER (ORDER BY month) FROM monthly;
$
SELECT order_id, customer_id, total, total / SUM(total) OVER (PARTITION BY customer_id) AS share FROM orders;Use case: Share of each order within a customer's total: % of customer spend.Example: SELECT order_id, customer_id, total, total / SUM(total) OVER (PARTITION BY customer_id) AS share FROM orders;
Modifying Data16
$
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com');Use case: Insert a single row.Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com');
$
INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com'), ('C', 'c@x.com');Use case: Insert multiple rows in one statement: much faster than separate inserts.Example: INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com');
$
INSERT INTO archive SELECT * FROM users WHERE created_at < '2020-01-01';Use case: Insert from a query: copy old rows into an archive table.Example: INSERT INTO archive SELECT * FROM users WHERE created_at < '2020-01-01';
$
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO NOTHING;Use case: Skip if the email already exists: idempotent imports (PostgreSQL).Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO NOTHING;
$
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;Use case: Upsert: update the existing row on conflict (PostgreSQL).Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
$
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON DUPLICATE KEY UPDATE name = VALUES(name);Use case: MySQL upsert: same idea, different syntax.Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON DUPLICATE KEY UPDATE name = VALUES(name);
$
UPDATE users SET status = 'active' WHERE id = 42;Use case: Update one row's column.Example: UPDATE users SET status = 'active' WHERE id = 42;
$
UPDATE users SET status = 'inactive' WHERE last_login_at < NOW() - INTERVAL '1 year';Use case: Bulk update with a condition: flag stale accounts.Example: UPDATE users SET status = 'inactive' WHERE last_login_at < NOW() - INTERVAL '1 year';
$
UPDATE orders SET total = total * 1.1 WHERE status = 'pending';Use case: Update using the existing value: apply a 10% surcharge.Example: UPDATE orders SET total = total * 1.1 WHERE status = 'pending';
$
UPDATE users SET email = NULL WHERE email = '';Use case: Normalize empty strings to NULL: clean up bad data.Example: UPDATE users SET email = NULL WHERE email = '';
$
DELETE FROM users WHERE id = 42;Use case: Delete one row.Example: DELETE FROM users WHERE id = 42;
$
DELETE FROM users WHERE last_login_at < NOW() - INTERVAL '5 years';Use case: Bulk delete: purge old records.Example: DELETE FROM users WHERE last_login_at < NOW() - INTERVAL '5 years';
$
DELETE FROM users;Use case: Delete ALL rows: table stays, data goes. Use with extreme care.Example: DELETE FROM users;
$
TRUNCATE TABLE users;Use case: Delete all rows instantly, resetting storage: much faster than DELETE for full clears.Example: TRUNCATE TABLE users;
$
UPDATE users SET plan = 'pro' FROM (SELECT id FROM users WHERE email LIKE '%@company.com') AS corp WHERE users.id = corp.id;Use case: Update from a subquery (PostgreSQL): upgrade a specific set.Example: UPDATE users SET plan = 'pro' FROM (SELECT id FROM users WHERE email LIKE '%@company.com') AS corp WHERE users.id = corp.id;
$
MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);Use case: Full upsert: insert new rows and update existing in one statement (SQL Server/Oracle).Example: MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
Schema & Indexes19
$
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT NOW());Use case: Create a table with constraints: the standard users table.Example: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT NOW());
$
ALTER TABLE users ADD COLUMN phone TEXT;Use case: Add a column to an existing table.Example: ALTER TABLE users ADD COLUMN phone TEXT;
$
ALTER TABLE users DROP COLUMN phone;Use case: Remove a column: and its data.Example: ALTER TABLE users DROP COLUMN phone;
$
ALTER TABLE users RENAME COLUMN phone TO mobile;Use case: Rename a column.Example: ALTER TABLE users RENAME COLUMN phone TO mobile;
$
ALTER TABLE users ALTER COLUMN name SET NOT NULL;Use case: Add a NOT NULL constraint: enforce required fields.Example: ALTER TABLE users ALTER COLUMN name SET NOT NULL;
$
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);Use case: Add a named unique constraint.Example: ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
$
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;Use case: Add a foreign key: cascade deletes customers' orders.Example: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;
$
CREATE INDEX idx_users_email ON users (email);Use case: Speed up lookups by email: index the columns you filter on.Example: CREATE INDEX idx_users_email ON users (email);
$
CREATE UNIQUE INDEX idx_users_email ON users (email);Use case: Index with a uniqueness guarantee: enforce dedup at the database level.Example: CREATE UNIQUE INDEX idx_users_email ON users (email);
$
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);Use case: Composite index: filter by customer and sort by date efficiently.Example: CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);
$
CREATE INDEX idx_orders_lower_email ON users (LOWER(email));Use case: Functional index: fast case-insensitive lookups (PostgreSQL).Example: CREATE INDEX idx_users_lower_email ON users (LOWER(email));
$
CREATE INDEX idx_users_name ON users USING GIN (to_tsvector('english', name));Use case: Full-text search index: fast text searching (PostgreSQL).Example: CREATE INDEX idx_users_name ON users USING GIN (to_tsvector('english', name));
$
DROP INDEX idx_users_email;Use case: Remove an index.Example: DROP INDEX idx_users_email;
$
DROP TABLE users;Use case: Delete a table permanently: no undo.Example: DROP TABLE users;
$
EXPLAIN SELECT * FROM users WHERE email = 'x@y.com';Use case: Show the query plan: is it using your index or scanning the whole table?Example: EXPLAIN SELECT * FROM users WHERE email = 'x@y.com';
$
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;Use case: Run the query AND show actual timings: find slow queries.Example: EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
$
CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';Use case: Save a query as a reusable view: treat it like a table.Example: CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';
$
CREATE MATERIALIZED VIEW monthly_revenue AS SELECT date_trunc('month', created_at) AS month, SUM(total) FROM orders GROUP BY 1;Use case: Precompute and store an aggregation (PostgreSQL): instant reads on big data.Example: CREATE MATERIALIZED VIEW monthly_revenue AS SELECT date_trunc('month', created_at) AS month, SUM(total) FROM orders GROUP BY 1;
$
REFRESH MATERIALIZED VIEW monthly_revenue;Use case: Rebuild a materialized view's data: schedule this after bulk loads.Example: REFRESH MATERIALIZED VIEW monthly_revenue;
Transactions & Advanced19
$
BEGIN; ... COMMIT;Use case: Wrap multiple statements in one atomic transaction: all succeed or all roll back.Example: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
$
BEGIN; ... ROLLBACK;Use case: Abort a transaction: undo everything since BEGIN.Example: BEGIN; UPDATE accounts SET balance = 0; ROLLBACK;
$
SELECT ... FOR UPDATE;Use case: Lock selected rows until the transaction ends: safe "transfer money" reads.Example: BEGIN; SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; ... COMMIT;
$
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;Use case: Isolate a transaction from concurrent changes: snapshot consistency.Example: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
$
SELECT NOW();Use case: Current timestamp: when is "now" on the database server?Example: SELECT NOW();
$
SELECT CURRENT_DATE;Use case: Today's date without time.Example: SELECT CURRENT_DATE;
$
SELECT DATE_TRUNC('month', NOW());Use case: Truncate a timestamp to the month (PostgreSQL): group by month cleanly.Example: SELECT DATE_TRUNC('month', NOW());
$
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';Use case: Rows from the last 7 days: rolling windows.Example: SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';
$
SELECT DATEDIFF('day', created_at, NOW()) FROM users;Use case: Days since an event (PostgreSQL DATEDIFF-style: EXTRACT(EPOCH FROM ...)).Example: SELECT EXTRACT(DAY FROM (NOW() - created_at)) FROM users;
$
WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days') SELECT customer_id, SUM(total) FROM recent GROUP BY customer_id;Use case: CTE: name an intermediate result for readable queries.Example: WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days') SELECT customer_id, SUM(total) FROM recent GROUP BY customer_id;
$
WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10) SELECT n FROM nums;Use case: Recursive CTE: generate sequences, trees, and hierarchies.Example: WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10) SELECT n FROM nums;
$
SELECT * FROM orders WHERE total = (SELECT MAX(total) FROM orders);Use case: Subquery: rows matching an aggregate condition.Example: SELECT * FROM orders WHERE total = (SELECT MAX(total) FROM orders);
$
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = users.id);Use case: EXISTS: users who have at least one order. Faster than IN on big tables.Example: SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = users.id);
$
SELECT * FROM users WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);Use case: IN with a subquery: users with big orders.Example: SELECT * FROM users WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);
$
SELECT CASE WHEN total > 1000 THEN 'high' WHEN total > 100 THEN 'medium' ELSE 'low' END AS tier, COUNT(*) FROM orders GROUP BY 1;Use case: CASE: bucket rows into categories, then aggregate.Example: SELECT CASE WHEN total > 1000 THEN 'high' WHEN total > 100 THEN 'medium' ELSE 'low' END AS tier, COUNT(*) FROM orders GROUP BY 1;
$
SELECT * FROM users UNION SELECT * FROM deleted_users;Use case: Combine results, deduplicating rows.Example: SELECT name FROM users UNION SELECT name FROM deleted_users;
$
SELECT * FROM users UNION ALL SELECT * FROM deleted_users;Use case: Combine results keeping duplicates: faster than UNION.Example: SELECT name FROM users UNION ALL SELECT name FROM deleted_users;
$
SELECT (json_data->>'name') FROM users;Use case: Extract a field from a JSON column (PostgreSQL): query JSON directly.Example: SELECT (json_data->>'name') FROM users;
$
SELECT * FROM users WHERE json_data @> '{"plan": "pro"}';Use case: Match rows by JSON content (PostgreSQL GIN): JSON filtering.Example: SELECT * FROM users WHERE json_data @> '{"plan": "pro"}';
SQL Cheatsheet
Every SQL command and clause you need: from SELECT to window functions, joins, aggregation, DDL, and transactions, with syntax and real use cases.
SQL is the language of relational databases. This cheatsheet covers the commands you write daily: querying with SELECT, filtering and sorting, every JOIN type, aggregation and GROUP BY, window functions, modifying data with INSERT/UPDATE/DELETE, and schema management with DDL.
Each entry shows real syntax followed by the use case. Most examples work in PostgreSQL, MySQL, SQLite, and SQL Server with minor dialect differences.
Querying Data
SELECT * FROM users;: Return every column and row from a table: the simplest read.Example: SELECT * FROM users;
SELECT id, name, email FROM users;: Return only specific columns: less data, faster queries.Example: SELECT id, name, email FROM users;
SELECT DISTINCT country FROM users;: Return unique values: which countries have users?Example: SELECT DISTINCT country FROM users;
SELECT name AS full_name FROM users;: Rename a column in the result: cleaner output.Example: SELECT name AS full_name FROM users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;: Combine columns into one (PostgreSQL/MySQL: || or CONCAT).Example: SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT 10 + 5 AS result;: Run calculations without a table: test expressions.Example: SELECT 10 + 5 AS result;
SELECT COALESCE(email, phone) FROM users;: Return the first non-NULL value: fallback columns.Example: SELECT COALESCE(email, phone) FROM users;
SELECT NULLIF(a, b);: Return NULL when two values are equal: avoid division by zero.Example: SELECT total / NULLIF(orders, 0) AS avg FROM stats;
SELECT COUNT(*) FROM users;: Count all rows: how many users exist?Example: SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;: Count unique values: how many countries are represented?Example: SELECT COUNT(DISTINCT country) FROM users;
SELECT * FROM users LIMIT 10;: Return only the first 10 rows: preview a large table.Example: SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;: Skip 20 rows, return 10: page 3 of results.Example: SELECT * FROM users LIMIT 10 OFFSET 20;
SELECT * FROM users ORDER BY created_at DESC LIMIT 1;: The latest user: most recent row first.Example: SELECT * FROM users ORDER BY created_at DESC LIMIT 1;
Filtering & Sorting
SELECT * FROM users WHERE status = 'active';: Filter rows with a condition.Example: SELECT * FROM users WHERE status = 'active';
SELECT * FROM orders WHERE total > 100 AND status = 'paid';: Combine conditions with AND: both must be true.Example: SELECT * FROM orders WHERE total > 100 AND status = 'paid';
SELECT * FROM users WHERE plan = 'free' OR plan = 'pro';: Either condition true: plan is one of these.Example: SELECT * FROM users WHERE plan = 'free' OR plan = 'pro';
SELECT * FROM users WHERE plan IN ('free', 'pro', 'team');: Cleaner than multiple ORs: value in a list.Example: SELECT * FROM users WHERE plan IN ('free', 'pro', 'team');
SELECT * FROM users WHERE email LIKE '%@gmail.com';: Pattern match: % is a wildcard. Find Gmail users.Example: SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name ILIKE 'jane%';: Case-insensitive pattern match (PostgreSQL): find Jane, jane, JANE.Example: SELECT * FROM users WHERE name ILIKE 'jane%';
SELECT * FROM users WHERE email IS NULL;: NULL comparison: = NULL never works, always use IS NULL.Example: SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';: Range filter: inclusive on both ends.Example: SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
SELECT * FROM orders WHERE total NOT BETWEEN 10 AND 100;: Exclude a range: orders outside 10-100.Example: SELECT * FROM orders WHERE total NOT BETWEEN 10 AND 100;
SELECT * FROM users ORDER BY created_at DESC;: Sort newest first.Example: SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY country ASC, name DESC;: Sort by multiple columns: country first, then name within country.Example: SELECT * FROM users ORDER BY country ASC, name DESC;
SELECT * FROM orders ORDER BY total DESC LIMIT 5;: Top 5 orders by value: your best customers' biggest orders.Example: SELECT * FROM orders ORDER BY total DESC LIMIT 5;
Joins
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;: INNER JOIN: only rows matching in both tables.Example: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;
SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;: Alias tables and pick columns: the everyday join.Example: SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id;
SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id;: All customers, even those with zero orders: NULLs fill the missing side.Example: SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id;
SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL;: Customers who NEVER ordered: the anti-join pattern.Example: SELECT * FROM customers LEFT JOIN orders ON orders.customer_id = customers.id WHERE orders.id IS NULL;
SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;: All customers (right table) with matching orders.Example: SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id;: Everything from both tables: matched or not (not in MySQL).Example: SELECT * FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.id;
SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id;: Self-join: employees with their managers, one table joined to itself.Example: SELECT * FROM employees e JOIN employees m ON e.manager_id = m.id;
SELECT * FROM a CROSS JOIN b;: Cartesian product: every row of a with every row of b.Example: SELECT * FROM sizes CROSS JOIN colors;
SELECT * FROM orders JOIN customers USING (customer_id);: Join when both tables share the same column name: less boilerplate.Example: SELECT * FROM orders JOIN customers USING (customer_id);
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id AND customers.country = 'US';: Join with extra conditions: filter inside the join.Example: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id AND customers.country = 'US';
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id;: Three-table join: orders with customer AND product details.Example: SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id JOIN products p ON o.product_id = p.id;
SELECT * FROM orders JOIN LATERAL (SELECT MAX(price) AS max_price FROM order_items WHERE order_id = orders.id) x ON true;: Lateral join: reference earlier tables inside a subquery (PostgreSQL).Example: SELECT * FROM orders JOIN LATERAL (SELECT MAX(price) FROM order_items WHERE order_id = orders.id) x ON true;
Aggregation
SELECT country, COUNT(*) FROM users GROUP BY country;: Count users per country: the fundamental GROUP BY.Example: SELECT country, COUNT(*) FROM users GROUP BY country;
SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 100;: Filter groups after aggregation: countries with 100+ users.Example: SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 100;
SELECT department, AVG(salary) FROM employees GROUP BY department;: Average salary per department.Example: SELECT department, AVG(salary) FROM employees GROUP BY department;
SELECT department, SUM(salary) FROM employees GROUP BY department;: Total payroll per department.Example: SELECT department, SUM(salary) FROM employees GROUP BY department;
SELECT department, MIN(salary), MAX(salary) FROM employees GROUP BY department;: Salary range per department.Example: SELECT department, MIN(salary), MAX(salary) FROM employees GROUP BY department;
SELECT COUNT(*), SUM(total) FROM orders;: Multiple aggregates in one pass: order count and revenue.Example: SELECT COUNT(*), SUM(total) FROM orders;
SELECT year, month, SUM(revenue) FROM sales GROUP BY year, month ORDER BY year, month;: Group by multiple columns: monthly revenue over years.Example: SELECT year, month, SUM(revenue) FROM sales GROUP BY year, month ORDER BY year, month;
SELECT GROUP_CONCAT(name) FROM users WHERE plan = 'pro';: Concatenate values into one string (MySQL: GROUP_CONCAT, PostgreSQL: string_agg).Example: SELECT GROUP_CONCAT(name) FROM users WHERE plan = 'pro';
SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;: Comma-joined list, sorted: tag lists and CSV building (PostgreSQL).Example: SELECT STRING_AGG(name, ', ' ORDER BY name) FROM users;
SELECT ARRAY_AGG(name) FROM users;: Collect values into an array (PostgreSQL): pass to client code.Example: SELECT ARRAY_AGG(name) FROM users;
SELECT JSON_AGG(name) FROM users;: Aggregate into a JSON array: build API payloads in SQL.Example: SELECT JSON_AGG(name) FROM users;
SELECT department, COUNT(*) FILTER (WHERE salary > 100000) FROM employees GROUP BY department;: Conditional count per group (PostgreSQL): senior staff per department.Example: SELECT department, COUNT(*) FILTER (WHERE salary > 100000) FROM employees GROUP BY department;
Window Functions
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;: Rank employees by salary: ties share a rank with gaps.Example: SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;: Rank without gaps: 1,2,2,3 instead of 1,2,2,4.Example: SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;: Unique sequential number per row: no ties.Example: SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;: Rank within each department separately: per-group leaderboards.Example: SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) FROM employees;
SELECT order_date, total, SUM(total) OVER (ORDER BY order_date) AS running_total FROM orders;: Cumulative running total over time.Example: SELECT order_date, total, SUM(total) OVER (ORDER BY order_date) AS running_total FROM orders;
SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3 FROM monthly;: 3-month moving average: smooth out seasonality.Example: SELECT month, revenue, AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3 FROM monthly;
SELECT name, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM employees;: Compare each row to the previous: month-over-month changes.Example: SELECT month, revenue, revenue - LAG(revenue) OVER (ORDER BY month) AS mom FROM monthly;
SELECT month, revenue, revenue - LEAD(revenue) OVER (ORDER BY month) AS next_diff FROM monthly;: Compare to the NEXT row: peek ahead.Example: SELECT month, revenue, revenue - LEAD(revenue) OVER (ORDER BY month) AS next_diff FROM monthly;
SELECT month, revenue, FIRST_VALUE(revenue) OVER (ORDER BY month) FROM monthly;: First value in the window: compare each month to January.Example: SELECT month, revenue, FIRST_VALUE(revenue) OVER (ORDER BY month) FROM monthly;
SELECT order_id, customer_id, total, total / SUM(total) OVER (PARTITION BY customer_id) AS share FROM orders;: Share of each order within a customer's total: % of customer spend.Example: SELECT order_id, customer_id, total, total / SUM(total) OVER (PARTITION BY customer_id) AS share FROM orders;
Modifying Data
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com');: Insert a single row.Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com');
INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com'), ('C', 'c@x.com');: Insert multiple rows in one statement: much faster than separate inserts.Example: INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com');
INSERT INTO archive SELECT * FROM users WHERE created_at < '2020-01-01';: Insert from a query: copy old rows into an archive table.Example: INSERT INTO archive SELECT * FROM users WHERE created_at < '2020-01-01';
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO NOTHING;: Skip if the email already exists: idempotent imports (PostgreSQL).Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO NOTHING;
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;: Upsert: update the existing row on conflict (PostgreSQL).Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON DUPLICATE KEY UPDATE name = VALUES(name);: MySQL upsert: same idea, different syntax.Example: INSERT INTO users (name, email) VALUES ('Jane', 'jane@x.com') ON DUPLICATE KEY UPDATE name = VALUES(name);
UPDATE users SET status = 'active' WHERE id = 42;: Update one row's column.Example: UPDATE users SET status = 'active' WHERE id = 42;
UPDATE users SET status = 'inactive' WHERE last_login_at < NOW() - INTERVAL '1 year';: Bulk update with a condition: flag stale accounts.Example: UPDATE users SET status = 'inactive' WHERE last_login_at < NOW() - INTERVAL '1 year';
UPDATE orders SET total = total * 1.1 WHERE status = 'pending';: Update using the existing value: apply a 10% surcharge.Example: UPDATE orders SET total = total * 1.1 WHERE status = 'pending';
UPDATE users SET email = NULL WHERE email = '';: Normalize empty strings to NULL: clean up bad data.Example: UPDATE users SET email = NULL WHERE email = '';
DELETE FROM users WHERE id = 42;: Delete one row.Example: DELETE FROM users WHERE id = 42;
DELETE FROM users WHERE last_login_at < NOW() - INTERVAL '5 years';: Bulk delete: purge old records.Example: DELETE FROM users WHERE last_login_at < NOW() - INTERVAL '5 years';
DELETE FROM users;: Delete ALL rows: table stays, data goes. Use with extreme care.Example: DELETE FROM users;
TRUNCATE TABLE users;: Delete all rows instantly, resetting storage: much faster than DELETE for full clears.Example: TRUNCATE TABLE users;
UPDATE users SET plan = 'pro' FROM (SELECT id FROM users WHERE email LIKE '%@company.com') AS corp WHERE users.id = corp.id;: Update from a subquery (PostgreSQL): upgrade a specific set.Example: UPDATE users SET plan = 'pro' FROM (SELECT id FROM users WHERE email LIKE '%@company.com') AS corp WHERE users.id = corp.id;
MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);: Full upsert: insert new rows and update existing in one statement (SQL Server/Oracle).Example: MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.name = s.name WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
Schema & Indexes
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT NOW());: Create a table with constraints: the standard users table.Example: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT NOW());
ALTER TABLE users ADD COLUMN phone TEXT;: Add a column to an existing table.Example: ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users DROP COLUMN phone;: Remove a column: and its data.Example: ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN phone TO mobile;: Rename a column.Example: ALTER TABLE users RENAME COLUMN phone TO mobile;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;: Add a NOT NULL constraint: enforce required fields.Example: ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);: Add a named unique constraint.Example: ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;: Add a foreign key: cascade deletes customers' orders.Example: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;
CREATE INDEX idx_users_email ON users (email);: Speed up lookups by email: index the columns you filter on.Example: CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email ON users (email);: Index with a uniqueness guarantee: enforce dedup at the database level.Example: CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);: Composite index: filter by customer and sort by date efficiently.Example: CREATE INDEX idx_orders_customer ON orders (customer_id, created_at);
CREATE INDEX idx_orders_lower_email ON users (LOWER(email));: Functional index: fast case-insensitive lookups (PostgreSQL).Example: CREATE INDEX idx_users_lower_email ON users (LOWER(email));
CREATE INDEX idx_users_name ON users USING GIN (to_tsvector('english', name));: Full-text search index: fast text searching (PostgreSQL).Example: CREATE INDEX idx_users_name ON users USING GIN (to_tsvector('english', name));
DROP INDEX idx_users_email;: Remove an index.Example: DROP INDEX idx_users_email;
DROP TABLE users;: Delete a table permanently: no undo.Example: DROP TABLE users;
EXPLAIN SELECT * FROM users WHERE email = 'x@y.com';: Show the query plan: is it using your index or scanning the whole table?Example: EXPLAIN SELECT * FROM users WHERE email = 'x@y.com';
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;: Run the query AND show actual timings: find slow queries.Example: EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 5;
CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';: Save a query as a reusable view: treat it like a table.Example: CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';
CREATE MATERIALIZED VIEW monthly_revenue AS SELECT date_trunc('month', created_at) AS month, SUM(total) FROM orders GROUP BY 1;: Precompute and store an aggregation (PostgreSQL): instant reads on big data.Example: CREATE MATERIALIZED VIEW monthly_revenue AS SELECT date_trunc('month', created_at) AS month, SUM(total) FROM orders GROUP BY 1;
REFRESH MATERIALIZED VIEW monthly_revenue;: Rebuild a materialized view's data: schedule this after bulk loads.Example: REFRESH MATERIALIZED VIEW monthly_revenue;
Transactions & Advanced
BEGIN; ... COMMIT;: Wrap multiple statements in one atomic transaction: all succeed or all roll back.Example: BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
BEGIN; ... ROLLBACK;: Abort a transaction: undo everything since BEGIN.Example: BEGIN; UPDATE accounts SET balance = 0; ROLLBACK;
SELECT ... FOR UPDATE;: Lock selected rows until the transaction ends: safe "transfer money" reads.Example: BEGIN; SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; ... COMMIT;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;: Isolate a transaction from concurrent changes: snapshot consistency.Example: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT NOW();: Current timestamp: when is "now" on the database server?Example: SELECT NOW();
SELECT CURRENT_DATE;: Today's date without time.Example: SELECT CURRENT_DATE;
SELECT DATE_TRUNC('month', NOW());: Truncate a timestamp to the month (PostgreSQL): group by month cleanly.Example: SELECT DATE_TRUNC('month', NOW());
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';: Rows from the last 7 days: rolling windows.Example: SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL '7 days';
SELECT DATEDIFF('day', created_at, NOW()) FROM users;: Days since an event (PostgreSQL DATEDIFF-style: EXTRACT(EPOCH FROM ...)).Example: SELECT EXTRACT(DAY FROM (NOW() - created_at)) FROM users;
WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days') SELECT customer_id, SUM(total) FROM recent GROUP BY customer_id;: CTE: name an intermediate result for readable queries.Example: WITH recent AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days') SELECT customer_id, SUM(total) FROM recent GROUP BY customer_id;
WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10) SELECT n FROM nums;: Recursive CTE: generate sequences, trees, and hierarchies.Example: WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < 10) SELECT n FROM nums;
SELECT * FROM orders WHERE total = (SELECT MAX(total) FROM orders);: Subquery: rows matching an aggregate condition.Example: SELECT * FROM orders WHERE total = (SELECT MAX(total) FROM orders);
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = users.id);: EXISTS: users who have at least one order. Faster than IN on big tables.Example: SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = users.id);
SELECT * FROM users WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);: IN with a subquery: users with big orders.Example: SELECT * FROM users WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);
SELECT CASE WHEN total > 1000 THEN 'high' WHEN total > 100 THEN 'medium' ELSE 'low' END AS tier, COUNT(*) FROM orders GROUP BY 1;: CASE: bucket rows into categories, then aggregate.Example: SELECT CASE WHEN total > 1000 THEN 'high' WHEN total > 100 THEN 'medium' ELSE 'low' END AS tier, COUNT(*) FROM orders GROUP BY 1;
SELECT * FROM users UNION SELECT * FROM deleted_users;: Combine results, deduplicating rows.Example: SELECT name FROM users UNION SELECT name FROM deleted_users;
SELECT * FROM users UNION ALL SELECT * FROM deleted_users;: Combine results keeping duplicates: faster than UNION.Example: SELECT name FROM users UNION ALL SELECT name FROM deleted_users;
SELECT (json_data->>'name') FROM users;: Extract a field from a JSON column (PostgreSQL): query JSON directly.Example: SELECT (json_data->>'name') FROM users;
SELECT * FROM users WHERE json_data @> '{"plan": "pro"}';: Match rows by JSON content (PostgreSQL GIN): JSON filtering.Example: SELECT * FROM users WHERE json_data @> '{"plan": "pro"}';
Frequently asked questions
What is the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN?
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all left-table rows plus matches from the right (NULL where missing). FULL OUTER JOIN returns all rows from both sides, matching where possible.
How do I find duplicate rows in a table?
Group by the columns that should be unique and count them: SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1. The HAVING clause filters after aggregation.
What is the difference between WHERE and HAVING?
WHERE filters rows before aggregation, so it cannot reference aggregate functions like COUNT or SUM. HAVING filters groups after aggregation. Use WHERE for row-level and HAVING for group-level conditions.
How do I paginate query results?
Use LIMIT <count> OFFSET <n> (PostgreSQL/MySQL/SQLite) or OFFSET ... FETCH NEXT <n> ROWS ONLY (SQL Server/Oracle). Order by a stable column first so pagination stays consistent.