SQL Queries & Commands Reference

SQL Commands & Queries Cheat Sheet

Essential SQL queries for developers, data analysts, and backend engineers. Searchable quick reference for joins, window functions, CTEs, aggregations, upserts, transactions, and index tuning.

SQL Formatter CSV ↔ JSON Linux Cheat Sheet

Showing 21 commands

querying

Basic Select with Aliases and Distinct

Format in SQL Formatter

SELECT DISTINCT status, COUNT(id) AS total_orders FROM orders WHERE created_at >= '2026-01-01' GROUP BY status ORDER BY total_orders DESC LIMIT 10 OFFSET 0;

Retrieves deduplicated status columns, counts orders, filters by date range, sorts in descending order, and paginates using LIMIT and OFFSET.

querying

Pattern Matching with Wildcards (LIKE and ILIKE)

SELECT id, email, username FROM users WHERE email ILIKE '%@company.org' AND username NOT LIKE 'test_%';

ILIKE performs case-insensitive wildcard search (PostgreSQL). Use LIKE '%text%' for standard ANSI SQL.

querying

Range and Set Inclusion (BETWEEN and IN)

SELECT product_name, price, stock FROM products WHERE price BETWEEN 19.99 AND 99.99 AND category_id IN (1, 4, 7, 12);

BETWEEN is inclusive of both boundary values. IN evaluates membership across a static tuple or dynamic subquery.

querying

Conditional Evaluation (CASE WHEN)

SELECT id, total_amount, CASE WHEN total_amount >= 500 THEN 'Platinum' WHEN total_amount >= 100 THEN 'Gold' ELSE 'Standard' END AS customer_tier FROM customer_orders;

Evaluates ordered boolean branches to compute derived values or categorical labels in result sets.

querying

Null Coalescing and Safe Handling

SELECT id, COALESCE(display_name, nickname, username, 'Anonymous') AS public_name, NULLIF(discount_code, '') AS sanitized_coupon FROM accounts;

COALESCE returns the first non-null argument. NULLIF returns NULL if both arguments are equal, converting empty strings to NULL.

joins

Inner Join (Exact matches across two tables)

SELECT u.id, u.email, o.id AS order_id, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE o.status = 'completed';

Returns only records where the join key matches in both tables.

joins

Left Outer Join (All left rows, matching right rows)

SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name HAVING COUNT(o.id) = 0;

Preserves every row from the left table; right columns are filled with NULL when no match exists. Useful for finding orphan records.

joins

Full Outer Join (All rows from both tables)

SELECT e.name AS employee, d.name AS department FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.id;

Returns all rows from both tables, filling NULLs on either side where keys do not match.

joins

Self Join (Hierarchical parent/child relationships)

SELECT emp.name AS employee_name, mgr.name AS manager_name FROM employees emp LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

Joins a table to itself using aliases to resolve hierarchical organizational charts or category trees.

aggregations

Group By with Having Clause

SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS head_count FROM employees WHERE is_active = true GROUP BY department_id HAVING AVG(salary) > 85000 AND COUNT(*) >= 5;

WHERE filters rows before aggregation. HAVING filters grouped rows after aggregate computation.

aggregations

Conditional Aggregation with FILTER / SUM CASE

SELECT COUNT(*) AS total_signups, COUNT(*) FILTER (WHERE plan = 'enterprise') AS enterprise_count, SUM(CASE WHEN verified = true THEN 1 ELSE 0 END) AS verified_users FROM user_profiles;

PostgreSQL FILTER clause or ANSI SQL CASE WHEN inside aggregate functions computes multi-metric pivots in a single scan.

analytics

Row Number, Rank, and Dense Rank

SELECT employee_id, department_id, salary, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank FROM salaries;

Partitions records by category and orders them. ROW_NUMBER assigns sequential integers; RANK leaves gaps on ties; DENSE_RANK leaves no gaps.

analytics

Period-over-Period Delta with LAG and LEAD

SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue, revenue - LAG(revenue, 1) OVER (ORDER BY month) AS monthly_delta FROM monthly_financials;

LAG accesses rows preceding the current row by an offset. LEAD accesses rows following the current row.

analytics

Running Cumulative Totals (Window Framing)

SELECT order_date, daily_sales, SUM(daily_sales) OVER ( ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM daily_metrics;

Computes cumulative sums across time dimensions using explicit window frames.

ctes

Common Table Expression (WITH Clause)

WITH high_value_customers AS ( SELECT user_id, SUM(amount) AS total_spend FROM transactions WHERE status = 'settled' GROUP BY user_id HAVING SUM(amount) > 10000 ) SELECT u.email, h.total_spend FROM users u INNER JOIN high_value_customers h ON u.id = h.user_id ORDER BY h.total_spend DESC;

Improves readability and reusability over nested subqueries by defining named temporary result sets.

ctes

Recursive CTE (Category Trees & Graph Traversal)

WITH RECURSIVE category_tree AS ( -- Anchor member: root categories SELECT id, name, parent_id, 1 AS depth FROM categories WHERE parent_id IS NULL UNION ALL -- Recursive member: child categories SELECT c.id, c.name, c.parent_id, ct.depth + 1 FROM categories c INNER JOIN category_tree ct ON c.parent_id = ct.id ) SELECT * FROM category_tree ORDER BY depth, name;

Recursively traverses hierarchical parent-child relationships, such as e-commerce categories or org charts.

modification

Upsert / On Conflict (Insert or Update)

INSERT INTO user_preferences (user_id, theme, email_notifications, updated_at) VALUES (42, 'dark', true, NOW()) ON CONFLICT (user_id) DO UPDATE SET theme = EXCLUDED.theme, email_notifications = EXCLUDED.email_notifications, updated_at = EXCLUDED.updated_at;

PostgreSQL ON CONFLICT (or MySQL ON DUPLICATE KEY UPDATE) executes an atomic insert-or-update without race conditions.

modification

Delete with Subquery / Using Join

DELETE FROM session_tokens WHERE expires_at < NOW() - INTERVAL '30 days' AND user_id IN ( SELECT id FROM users WHERE status = 'suspended' );

Prunes stale session records linked to deactivated accounts with interval date arithmetic.

schema

Create Table with Constraints and Foreign Keys

CREATE TABLE orders ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, order_number VARCHAR(64) UNIQUE NOT NULL, total_cents INTEGER NOT NULL CHECK (total_cents >= 0), status VARCHAR(32) DEFAULT 'pending', created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW() );

Defines strict schema invariants: primary keys, cascade foreign keys, unique bounds, and check constraints.

schema

Create Partial and Composite Indexes

-- Composite B-tree index for query equality and range CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC); -- Partial index for active queries (smaller footprint) CREATE INDEX idx_orders_unprocessed ON orders (created_at) WHERE status IN ('pending', 'processing');

Composite indexes satisfy multiple WHERE and ORDER BY columns. Partial indexes only index matching rows, reducing index disk footprint.

transactions

ACID Transaction with Savepoints

BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; SAVEPOINT debit_done; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- If error occurs: ROLLBACK TO SAVEPOINT debit_done; COMMIT;

Wraps multi-table mutations in an atomic block. Savepoints allow partial rollbacks without abandoning the entire transaction.

Frequently Asked Questions About SQL Queries & Optimization

What is the difference between WHERE and HAVING in SQL?

WHERE filters rows before any groupings or aggregate functions (COUNT, SUM, AVG) are computed. HAVING filters the grouped summary rows after the GROUP BY clause has been evaluated.

When should I use ROW_NUMBER() vs RANK() vs DENSE_RANK()?

All three compute ordinal rankings within an OVER (PARTITION BY ... ORDER BY ...) window. ROW_NUMBER assigns distinct sequential numbers (1, 2, 3, 4). RANK assigns identical numbers to ties and skips subsequent numbers (1, 2, 2, 4). DENSE_RANK assigns identical numbers to ties without skipping numbers (1, 2, 2, 3).

What is the performance difference between UNION and UNION ALL?

UNION combines result sets from two queries and automatically runs an expensive sorting and deduplication step. UNION ALL simply concatenates the result sets without deduplication, making it significantly faster and less memory-intensive when rows are guaranteed unique.

Why are Common Table Expressions (CTEs) preferred over deeply nested subqueries?

CTEs (using the WITH clause) make SQL scripts modular, linear, and readable from top to bottom. They can also be referenced multiple times within the same statement and support recursion for hierarchical tree traversal.

What does an atomic UPSERT (ON CONFLICT DO UPDATE) accomplish?

An UPSERT avoids race conditions between concurrent worker threads trying to insert the same unique key simultaneously. Instead of throwing a unique constraint violation error, the database updates the existing row in place atomically.