LaoHandDeveloper Cheatsheet

SQL Cheatsheet - Common Query Syntax & Examples

For developers who write SQL that is both business-correct and fast. The traps are usually not in the syntax itself but in NULL propagation, filtering after GROUP BY requiring HAVING, and where window functions stop overlapping plain aggregation. By the end you can write correct queries with grouping and filtering clauses, use window functions for row numbers and in-group ranking instead of self-joins, dodge NULL comparisons and aggregation pitfalls, and wrap multi-statement work in transactions for atomicity.

Databases·35 commands·Last updated 2026-08-02
sqldatabaseQuerydb

Typical Use Case

For back-end developers who need queries that are both correct and fast. SQL rarely goes wrong in the syntax itself — it goes wrong in how NULL propagates, in filtering after GROUP BY requiring HAVING, in how window functions differ from plain aggregation, and in row multiplication from one-to-many JOINs. This sheet is organised as a debugging checklist: six groups (basic queries, filtering, aggregation, joins, window functions, transactional writes), each annotated with when it gets slow or when it gets wrong.

Basic Query 6

SELECT * FROM users
Select all columns
SELECT name, age FROM users
Select specific columns
SELECT * FROM users WHERE age > 18
Filter with condition
SELECT DISTINCT city FROM users
Remove duplicates
SELECT * FROM users LIMIT 10 OFFSET 20
Pagination (skip 20, take 10)
SELECT * FROM users ORDER BY age DESC
Sort descending

Filtering & Logic 6

WHERE name LIKE 'A%'
Prefix match
WHERE name LIKE '%son'
Suffix match
WHERE age BETWEEN 18 AND 30
Range
WHERE id IN (1, 2, 3)
Set membership
WHERE a IS NULL
Null check
WHERE a AND b OR c
Logic (AND precedes OR)

Aggregation & Grouping 5

SELECT COUNT(*) FROM users
Count
SELECT AVG(age) FROM users
Average
SELECT city, COUNT(*) FROM users GROUP BY city
Group by city
HAVING COUNT(*) > 5
Filter after grouping (on aggregates)
SELECT MAX(age), MIN(age) FROM users
Max / min

JOIN 5

FROM a JOIN b ON a.id = b.a_id
Inner join
FROM a LEFT JOIN b ON a.id = b.a_id
Left join (keep all of a)
FROM a RIGHT JOIN b ON a.id = b.a_id
Right join
FROM a CROSS JOIN b
Cartesian product
SELECT * FROM a NATURAL JOIN b
Auto-join on same-named columns

Window Functions 4

ROW_NUMBER() OVER (ORDER BY age)
Row number
RANK() OVER (PARTITION BY city ORDER BY age)
Rank (gaps on ties)
SUM(amount) OVER (PARTITION BY user_id)
Partitioned running total
LAG(price) OVER (ORDER BY date)
Previous row value

DML & Transactions 5

INSERT INTO t (a, b) VALUES (1, 'x')
Insert
UPDATE t SET a = 2 WHERE id = 1
Update
DELETE FROM t WHERE id = 1
Delete
BEGIN; ... COMMIT;
Commit transaction
ROLLBACK;
Rollback transaction

DDL 4

CREATE TABLE t (id INT PRIMARY KEY, name TEXT)
Create table
ALTER TABLE t ADD COLUMN age INT
Add column
DROP TABLE t
Drop table
CREATE INDEX idx_name ON t(name)
Create index

Parameter matrix

ParameterEffectExample
LIKE 'A%'Prefix match. Because the wildcard is trailing, a B-tree index can still be used.WHERE name LIKE 'A%'
LIKE '%son'Suffix match. A leading % makes the pattern unindexable, so the engine falls back to a scan.WHERE name LIKE '%son'
IS NULLTests for a missing value. Remember that a = NULL is never true, it evaluates to UNKNOWN.WHERE deleted_at IS NULL
GROUP BYCollapse rows that share a key into a single row so aggregates can be computed per group.GROUP BY city
HAVINGFilter after grouping, which is the only place aggregate results such as COUNT(*) can be tested.HAVING COUNT(*) > 5
LEFT JOINKeep every row of the left table and attach matching right-hand rows where they exist.a LEFT JOIN b ON a.id = b.a_id
ROW_NUMBER()A window function that ranks rows per partition without collapsing the underlying detail.ROW_NUMBER() OVER (PARTITION BY city ORDER BY age)
LAG(col)Read the value from the previous row, which is how period-over-period comparisons are built.LAG(price) OVER (ORDER BY date)
BEGIN / COMMITTransaction control: several statements land atomically and can be rolled back as one unit.BEGIN; UPDATE ...; COMMIT;
CREATE INDEXSpeed up reads on a column at the cost of slower writes and a larger on-disk footprint.CREATE INDEX idx_name ON t(name)
LIMIT n OFFSET mPaginate results. A large OFFSET still forces the engine to walk and discard every skipped row.LIMIT 10 OFFSET 20
COUNT(*) vs COUNT(col)COUNT(*) counts rows, while COUNT(col) counts only rows where col is not NULL.COUNT(*) FROM t

Common pitfalls

SymptomWHERE a = NULL returns nothing even though the table clearly holds NULL rows.

CauseNULL means "value unknown", so any comparison against it yields UNKNOWN rather than TRUE, and WHERE only keeps TRUE rows.

FixUse WHERE a IS NULL or IS NOT NULL to test for missing values.

SymptomCounting orders after joining them to line items returns far more orders than actually exist.

CauseA one-to-many join duplicates the parent row N times, and COUNT(*) then counts the duplicates.

FixAggregate the child table first and join that result, or use COUNT(DISTINCT parent.id).

SymptomWrapping a column in a function inside WHERE turns a sub-second query into a multi-minute one.

CauseApplying a function or expression to the column makes the index unusable, so the plan degrades to a full scan.

FixRewrite it as a range condition such as created_at >= ? AND created_at < ? so the index can be used.

SymptomAfter a CREATE INDEX or ALTER TABLE inside a transaction, the transaction can no longer be rolled back.

CauseIn most databases DDL implicitly commits the surrounding transaction.

FixMove DDL outside the transaction and avoid mixing DDL into long-running transactions.

SymptomPaging deep into a result set, such as OFFSET 100000, suddenly becomes extremely slow.

CauseThe database still has to scan and throw away the first 100,000 rows before it can return the page.

FixSwitch to cursor-based pagination: WHERE id > last_id_of_previous_page ORDER BY id LIMIT n.

SymptomSelecting an ungrouped column after GROUP BY raises "column must appear in the GROUP BY list".

CauseStandard SQL requires every non-aggregated column in the select list to be part of the grouping key.

FixAdd the column to GROUP BY, or wrap it in an aggregate such as MAX(name).

SymptomA LIKE '%keyword%' search times out once the table gets large.

CauseThe leading wildcard gives a B-tree index no prefix to seek on, so the whole table must be scanned.

FixUse a full-text index (MySQL FULLTEXT / PostgreSQL tsvector) or hand the search off to a search engine.

Troubleshooting

  1. 1First confirm whether the query really uses an index

    EXPLAIN SELECT * FROM orders WHERE customer_id = 42

    Read the type column: ALL means a full table scan, and Using index in Extra is what marks a covering index hit.

  2. 2Verify that the null-check syntax is written correctly

    SELECT COUNT(*) FROM t WHERE deleted_at IS NULL

    It must be IS NULL, not = NULL; counting both ways confirms you did not flip the condition.

  3. 3Locate the execution plan behind a slow query

    EXPLAIN ANALYZE SELECT city, COUNT(*) FROM users GROUP BY city

    EXPLAIN ANALYZE actually executes the statement and reports time per stage, which shows whether sorting or aggregation dominates.

  4. 4Measure how much a join inflates the row count

    SELECT COUNT(*) FROM a JOIN b ON a.id = b.a_id

    Compare it with SELECT COUNT(*) FROM a; the difference is the one-to-many amplification factor.

  5. 5Add an index for a frequently filtered column

    CREATE INDEX idx_orders_customer ON orders(customer_id, created_at)

    A composite index follows the leftmost-prefix rule, so order the columns after your real query patterns, then re-run EXPLAIN to confirm.

Command Examples

Testing for NULL correctly

SELECT name FROM users WHERE deleted_at IS NULL -- 错误写法：WHERE deleted_at = NULL 恒返回空结果

NULL 表示「值未知」，任何与它的比较都得到 UNKNOWN 而非 TRUE。必须用 IS NULL / IS NOT NULL，这是 SQL 三值逻辑最常见的陷阱。

Output

alice
bob
carol

Filter after grouping with HAVING

SELECT city, COUNT(*) AS cnt FROM users GROUP BY city HAVING COUNT(*) > 5 ORDER BY cnt DESC

WHERE 在分组前过滤行且不能用聚合函数，通常能走索引；HAVING 在 GROUP BY 之后，可以引用 COUNT/AVG。先用 WHERE 缩小范围再 HAVING 更快。

Output

city       | cnt
-----------+-----
Shanghai   |  412
Beijing    |  287
Shenzhen   |  163

Rank with a window function, no subquery needed

SELECT name, city, age, ROW_NUMBER() OVER (PARTITION BY city ORDER BY age DESC) AS rnk FROM users ORDER BY city, rnk

GROUP BY 会把多行压成一行并丢失明细，窗口函数在保留每行的同时计算聚合值。每组取前 N 条、累计求和这类需求用窗口函数一步到位。

Output

name   | city     | age | rnk
--------+----------+-----+-----
ali    | Beijing  |  34 |   1
bo     | Beijing  |  28 |   2
cat    | Shanghai |  31 |   1

A one-to-many join multiplies rows

SELECT u.name, COUNT(i.id) AS items FROM users u LEFT JOIN order_items i ON i.user_id = u.id GROUP BY u.name ORDER BY items DESC

订单 join 明细时父表字段会重复出现，直接 count(*) 会多算。要么只 select 需要的父字段，要么先对子表聚合再 join。

Output

name  | items
------+-------
ali   |    12
bo    |     3
cat   |     1

Common Pitfalls

  • WHERE a = NULL never matches; null checks require IS NULL / IS NOT NULL.
  • Joining a one-to-many table multiplies parent rows and inflates counts; aggregate the child table first.
  • Applying a function or expression to a column in WHERE defeats the index — rewrite as a range predicate.
  • A leading wildcard (LIKE %abc) cannot use an ordinary index; use full-text search at scale.
  • Unbounded queries or UPDATE/DELETE without LIMIT can lock tables and stall a production database; check the affected row count first.
  • DDL such as CREATE INDEX implicitly commits and cannot be rolled back — avoid it inside long transactions.

Tips

  • AND has higher precedence than OR — use parentheses for complex conditions.
  • Columns in GROUP BY must appear in SELECT (except aggregated columns).
  • LIKE case-sensitivity depends on the database collation.
  • Wrap data changes in BEGIN; and COMMIT only after verifying.

FAQ

Why does a = NULL never match anything?

In SQL, NULL means the value is unknown rather than some particular value, so any comparison with NULL evaluates to UNKNOWN instead of TRUE and the row is excluded. You must use IS NULL and IS NOT NULL. This is the classic three-valued-logic trap, and treating NULL as if it were 0 or an empty string is the most common source of these bugs.

What is the difference between WHERE and HAVING?

WHERE filters raw rows before grouping; it operates per row, cannot reference aggregates, and can usually use an index. HAVING filters after GROUP BY and can reference values such as count or avg. Prefer WHERE to reduce rows early, and reach for HAVING only when the condition depends on an aggregate, such as HAVING COUNT(*) > 5.

Why does row count grow when joining one-to-many?

Each parent row matches N child rows, so the result is 1×N rather than 1. An order joined to its line items yields three rows for a three-item order, repeating the parent columns each time. Select only the parent fields you need, or aggregate the child table before joining — otherwise counts double, since count(*) over the joined result counts duplicates.

When should I add an index?

Base it on query patterns, not intuition: fields appearing in WHERE, JOIN and ORDER BY are candidates, and only if they show up in slow queries. Indexes slow writes and consume storage, so more is not better. A practical starting point is the primary key, foreign keys and frequently filtered columns — then verify with the execution plan that they are actually used.

What do window functions offer over GROUP BY?

GROUP BY collapses many rows into one and discards the detail; window functions compute aggregates while keeping every row. ROW_NUMBER() OVER (ORDER BY age) ranks rows, SUM(amount) OVER (PARTITION BY user_id) accumulates within a partition, and LAG(price) OVER (ORDER BY date) pulls the previous value. Ranking, latest-per-group and running totals need workarounds with GROUP BY but are direct with window functions.

Related Cheatsheets

6 related cheatsheets

MySQL Cheatsheet - Queries, Joins, Users & IndexesA MySQL command handbook for DBAs, backends, and data analysts, covering database & table management, CRUD, index optimization and slow-query localization with EXPLAIN, user privileges, logical backup & recovery, and replication — highlighting operations that can lock tables or stall replication.PostgreSQL Cheatsheet - PostgreSQL Command ReferenceA PostgreSQL command handbook for backend engineers, DBAs, and data analysts, covering connection & role management, database & table operations, EXPLAIN analysis, lock & transaction troubleshooting, pg_dump/pg_restore backup & recovery, and streaming replication, with paths for locating lock waits and table bloat.Redis Cheatsheet - Keys, Strings, Hash & Expire CommandsA Redis command handbook for the cache/backend and its operators, covering string and per-data-structure operations, key space and TTL management, RDB/AOF persistence trade-offs, memory and big-key inspection, slow query monitoring, cluster & replication, and practical debugging of hot keys, big keys, and cache puncturing/cascading.MongoDB Cheatsheet - MongoDB Command ReferenceA MongoDB reference for backend engineers, covering database & collection management, document CRUD, query operators, index strategies that keep queries off full collection scans, the aggregation pipeline, and replica-set vs sharded-cluster responsibilities.Neo4j Cypher Cheatsheet - Neo4j Graph Database Command ReferenceA Neo4j Cypher reference for working with graph data where relationships matter more than rows, covering node & relationship creation, pattern matching, updates & deletes, aggregation and ordering, constraints & indexes, and traversal-style graph queries.SQL JOIN Cheatsheet - SQL JOIN Query ReferenceA SQL JOIN reference for writing multi-table queries correctly, covering INNER, LEFT/RIGHT/FULL OUTER, CROSS, self-joins, USING/natural joins, and when a correlated subquery with EXISTS beats a JOIN — plus the row-duplication pitfall of joins on one-to-many tables.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Maintained by LaoHand

Publicly updated on Aug 2, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.

Contact Us