SQL Cheat Sheet: Queries, Joins, Aggregates

SQL is the standard language for working with relational databases, and being fluent in sql queries is one of the most transferable skills a backend developer can build. This cheatsheet covers the commands you’ll reach for most often — from basic SELECT statements through aggregates, JOINs, window functions, and data modification. Every snippet runs on PostgreSQL, MySQL, and SQLite unless noted.

If you’re deciding between SQL and NoSQL storage, the SQL vs NoSQL comparison covers the trade-offs in depth.

SQL Queries: SELECT, WHERE, and Filtering

Every sql query starts with SELECT. The clause after FROM names the table; WHERE restricts which rows come back.

-- Basic SELECT: retrieve specific columns
SELECT first_name, last_name, email
FROM users
WHERE status = 'active'
ORDER BY last_name ASC
LIMIT 50;

SELECT Modifiers

WHERE Operators

Wildcards in LIKE: % matches any sequence of characters; _ matches exactly one character. PostgreSQL also supports ILIKE for case-insensitive matching.

JOIN Types: INNER, LEFT, RIGHT, and FULL

Joins combine rows from two or more tables based on a related column. The most common pattern joins on foreign keys — columns that reference a primary key in another table. Understanding which join type fits your intent keeps the result set correct.

-- INNER JOIN: only rows matching on both sides
SELECT o.order_id, o.total, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped';

-- LEFT JOIN: all orders, NULL for missing user record
SELECT o.order_id, u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;

JOIN Quick Reference

For worked examples and edge cases of the CROSS JOIN type, see the CROSS JOIN guide.

Multi-Table Joins

You can chain JOINs. The planner processes them left to right:

SELECT o.order_id, u.email, p.name AS product_name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON oi.order_id = o.order_id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed';

GROUP BY and Aggregate Functions

GROUP BY collapses rows that share the same column value into a single output row, letting aggregate functions summarise each group.

-- Orders per customer — only customers with more than 5 orders
SELECT user_id,
       COUNT(*) AS order_count,
       SUM(total) AS revenue,
       AVG(total) AS avg_order_value
FROM orders
WHERE status != 'cancelled'
GROUP BY user_id
HAVING COUNT(*) > 5
ORDER BY revenue DESC;

WHERE filters rows before grouping. HAVING filters groups after aggregation — it is the only place you can filter on aggregate results. You cannot reference a SELECT alias inside HAVING; use the full aggregate expression instead.

Aggregate Function Reference

For the full list of aggregate functions in PostgreSQL, see the PostgreSQL aggregate functions reference.

Subqueries and Common Table Expressions

A subquery is a SELECT nested inside another statement. A Common Table Expression (CTE) uses the WITH clause to name the subquery upfront, making complex sql queries far easier to read and debug.

-- Scalar subquery in SELECT
SELECT
  product_id,
  price,
  price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;

-- CTE version of a correlated filter
WITH high_value AS (
  SELECT user_id
  FROM orders
  WHERE total > 500
)
SELECT u.first_name, u.email
FROM users u
INNER JOIN high_value hv ON u.id = hv.user_id;

CTEs can be chained — define multiple comma-separated blocks inside a single WITH clause, each able to reference the ones before it. Recursive CTEs (using WITH RECURSIVE) walk tree structures like category hierarchies or org charts without any procedural code.

A correlated subquery references a column from the outer query and re-executes for every outer row — performance degrades on large tables. Prefer a JOIN or a CTE when you can; they give the query planner more to work with.

Window Functions

Window functions compute a value for each row based on a set of related rows, without collapsing those rows into a group. They are one of the most useful additions to SQL in the last two decades and are supported by PostgreSQL, MySQL 8+, SQL Server, and SQLite 3.25+. The SQLite window functions documentation has full syntax details.

-- Rank customers by total spend within each country
SELECT
  user_id,
  country,
  total_spend,
  RANK() OVER (PARTITION BY country ORDER BY total_spend DESC) AS country_rank,
  SUM(total_spend) OVER (PARTITION BY country) AS country_total,
  ROW_NUMBER() OVER (ORDER BY total_spend DESC) AS global_rank
FROM customer_stats;

Window Function Reference

PARTITION BY divides the window into independent subsets. Omitting it treats the entire result set as one window. ORDER BY inside OVER (...) defines which rows precede the current row within each partition.

INSERT, UPDATE, and DELETE

These DML (Data Manipulation Language) statements modify data. Wrap destructive operations in a transaction so you can roll back if something goes wrong.

-- Insert a single row
INSERT INTO products (name, price, stock)
VALUES ('USB-C Hub', 29.99, 150);

-- Insert multiple rows in one statement
INSERT INTO products (name, price, stock)
VALUES ('HDMI Cable', 9.99, 500),
       ('Webcam', 79.00, 75);

-- Update with a condition (always include WHERE unless intentional)
UPDATE products
SET price = 24.99, updated_at = NOW()
WHERE name = 'USB-C Hub';

-- Delete matching rows
DELETE FROM products
WHERE stock = 0 AND discontinued = true;

Upsert Patterns

PostgreSQL and SQLite support INSERT … ON CONFLICT to insert or update in a single statement:

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

MySQL uses INSERT … ON DUPLICATE KEY UPDATE for the same effect. SQL Server uses MERGE.

DELETE is row-by-row and transaction-safe — you can roll it back. TRUNCATE removes all rows by freeing data pages, which is far faster on large tables but cannot be rolled back in MySQL (PostgreSQL allows TRUNCATE inside a transaction).

Format Your SQL

Before sharing or committing a query, paste it into the SQL Formatter to auto-indent and normalise spacing. Consistent formatting surfaces missing WHERE clauses and stray conditions that are easy to miss in dense sql queries.

Indexes and Query Performance

The speed of sql queries on large tables depends on SQL indexes. An index is a separate data structure the database maintains so it can find rows without scanning the entire table.

Practical indexing rules:

• Index columns used in WHERE, JOIN ON, and ORDER BY clauses that run frequently.
• Always index foreign key columns — unindexed FK joins are among the most common performance problems in production schemas.
• Avoid indexing low-cardinality columns (boolean flags, status columns with 2–3 distinct values) — the planner typically ignores those indexes.
• Use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to see whether queries are hitting an index or falling through to a full table scan.

For the complete SQL command syntax, the PostgreSQL SQL commands reference and the MySQL 8.0 SQL statements guide are the authoritative sources.

Frequently Asked Questions

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before GROUP BY runs; HAVING filters the grouped results afterward. You can only filter on aggregate function results (COUNT, SUM, AVG, etc.) inside HAVING, not WHERE. You also cannot reference SELECT column aliases in WHERE — the alias does not exist yet at that stage of query execution. Use HAVING only when the condition depends on an aggregate value.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where there is a matching row in both tables — left-side rows with no corresponding right-side row are excluded entirely. LEFT JOIN returns every row from the left table, with NULL substituted for columns from the right table wherever no match exists. Use INNER JOIN when the relationship is required; use LEFT JOIN when you need all records from the primary table regardless of whether a related record exists on the other side.

What are window functions in SQL?

Window functions compute a value for each row based on a set of related rows (the window) without collapsing those rows into a single grouped output. The OVER (...) clause defines the window — PARTITION BY creates independent subsets, ORDER BY inside OVER determines row ordering within each partition. ROW_NUMBER, RANK, LAG, LEAD, and running aggregates such as SUM() OVER (...) are the most commonly used. They are available in PostgreSQL, MySQL 8+, SQL Server, and SQLite 3.25+.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows matching a WHERE condition (or all rows without one) and logs each deletion individually, making it fully transaction-safe. TRUNCATE removes all rows by freeing data pages without logging individual row deletions — much faster on large tables, but not reversible in MySQL. PostgreSQL supports TRUNCATE inside a transaction. Use DELETE when you need a WHERE condition, want rollback safety, or need triggers to fire. Use TRUNCATE when you need to empty a large table as quickly as possible.

Conclusion

SQL queries are the foundation of working with relational data. The patterns here — SELECT with filtering, JOIN types, GROUP BY with aggregates, window functions, and DML statements — cover the majority of what you’ll write day to day. When sql queries get complex, CTEs keep them readable. When they get slow, run EXPLAIN and consult the SQL indexes guide for optimisation strategies. Keep the SQL Formatter open to maintain consistent style across your codebase.