SQL Cheat Sheet
SQL cheat sheet: syntax and examples of common statements — SELECT, JOIN, GROUP BY, INSERT, UPDATE, DELETE, aggregates and constraints — for quick lookup while writing queries.
How It Works
SQL is the standard declarative language for relational databases: you state what you want and the optimizer decides how to fetch it. DML (SELECT/INSERT/UPDATE/DELETE) handles data, DDL (CREATE/ALTER/DROP) defines structure, DCL (GRANT) manages rights.
Data lives in tables (rows/columns) linked by foreign keys; JOIN assembles multiple tables into a result set. Indexes speed reads but slow writes; transactions guarantee concurrency safety via ACID (Atomic, Consistent, Isolated, Durable). Prefer parameterized queries to prevent SQL injection.
When to Use This Table
This table is for looking up SQL syntax without opening documentation. Its value is coverage: DDL, DML, aggregates, window functions and transaction control are grouped so a missing piece is easy to locate. Dialect differences are the catch, though — the same "top N rows" is LIMIT in MySQL, TOP n in SQL Server and ROWNUM in Oracle, so cross-database work requires translation.
When not to consult it: you are tuning query performance. Index types, execution plans and cost models belong to database tuning, and a syntactically correct full-table scan is far slower than a syntax error. This table offers no performance criteria at all.
Rule of thumb: this table is enough for syntax assembly, report queries and one-off exports. For isolation levels, locking behaviour and concurrent writes, read your specific database documentation instead of generic syntax — the same UPDATE can behave completely differently under different isolation levels.
Mind SQL injection: string concatenation instead of parameter binding remains the most common backend vulnerability. The syntax here is not itself a problem, but any writing that interpolates a variable should use a prepared statement rather than wrapping the value in quotes.
Boundaries and Common Pitfalls
NULL in any comparison yields NULL
This is the most counter-intuitive rule: NULL <> 1 evaluates to NULL, not true, so WHERE filters the row out. Write IS NULL or IS NOT NULL explicitly. Aggregates also ignore NULL (COUNT(column) skips nulls while COUNT(*) counts rows), and AVG or SUM over an all-NULL set returns NULL rather than zero.
DELETE and TRUNCATE differ far more than expected
DELETE removes row by row inside a transaction, is rollback-capable and can target a subset via WHERE. TRUNCATE wipes the whole table, is usually not rollback-capable depending on database and engine, resets identity columns, and does not fire DELETE triggers. With foreign keys TRUNCATE typically fails outright while DELETE can proceed from child tables first. Mismatching them loses production data or fails unexpectedly.
An UPDATE without WHERE empties the table
This happens every year. Practical guards: run the equivalent SELECT first to see the affected row count; configure the database to reject or require explicit confirmation for a WHERE-less UPDATE; or restrict UPDATE and DELETE to views or stored procedures that require a condition. Relying on care alone does not work.
NULL semantics in JOINs are easy to miss
Writing a right-table condition in WHERE instead of ON silently degrades a LEFT JOIN into an INNER JOIN, because NULL <> x is NULL and therefore not true. Conditions belong in the ON clause. Separately, COUNT(right.column) under a LEFT JOIN does not count unmatched rows, so counting total rows requires COUNT(*) rather than COUNT(column) — a frequent root cause of mismatched report totals.
How This Relates to Neighbours
For dialect differences and version-specific behaviour of MySQL, PostgreSQL, SQLite or SQL Server, consult that database official docs; this table only covers common syntax.
To format or validate a statement, use the in-site SQL tools; this table has no parsing or highlighting.
Understanding why an index is not used and reading EXPLAIN requires database tuning knowledge beyond this table.
If the goal is safely exporting results (quoting, delimiters, encoding), that is an export pipeline problem rather than a SQL syntax one.
Practical Snippet
Key points: (1) run the equivalent SELECT first to check the affected row count, wrap the write in a transaction and commit only after verifying; (2) NULL can only be tested with IS NULL — = NULL is never true; (3) right-side conditions in a LEFT JOIN belong in ON, since putting them in WHERE degrades it to an INNER JOIN, and count rows with COUNT(*) not COUNT(column); (4) always bind parameters — string concatenation is the main SQL injection vector.
-- ① 改写前先看影响面：把 UPDATE 的 WHERE 套进 SELECT
SELECT id, name, status
FROM orders
WHERE status = 'pending' AND created_at < '2024-01-01';
-- 确认行数合理，再执行 UPDATE；行数异常就是条件写错了
BEGIN;
UPDATE orders SET status = 'expired'
WHERE status = 'pending' AND created_at < '2024-01-01';
-- 核对受影响行数与上一步一致
COMMIT; -- 不满意就 ROLLBACK;
-- ② NULL 必须显式判断
SELECT name FROM users WHERE manager_id IS NULL; -- 不是 = NULL
SELECT name FROM users WHERE manager_id IS NOT NULL;
-- ③ LEFT JOIN 的条件写在 ON，不要写 WHERE
SELECT c.name AS customer, o.total
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status = 'paid'; -- 放在 ON 里才保留未付费客户
-- 统计总行数用 COUNT(*)，不是 COUNT(o.total)
SELECT COUNT(*) AS customers, COUNT(o.total) AS paid_rows
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid';
-- ④ 用参数绑定，不要拼字符串（SQL 注入防线）
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?';
SET @email = 'a@example.com';
EXECUTE stmt USING @email;
DEALLOCATE PREPARE stmt;Practical Notes
- Any comparison with NULL yields UNKNOWNWHERE col = NULL never matches; use IS NULL. Likewise, if a NOT IN subquery returns any NULL, the whole predicate becomes UNKNOWN and the result set comes back empty — a classic "the rows exist but the query finds nothing" bug.
- COUNT(*) and COUNT(col) mean different thingsCOUNT(*) counts rows; COUNT(col) counts only rows where that column is not NULL. Mixing them up skews null-rate analysis, and COUNT(DISTINCT col) drops NULLs as well.
- Implicit casts disable indexesWHERE varchar_col = 123 makes most databases cast the column side to a number, so the index is unusable and the query becomes a full scan. Keep parameter types aligned with column types, including collation and character set.
Data Sources
- ISO/IEC 9075 (SQL)
- Database vendor dialect documentation
Codes, names and definitions above come from public specifications; we claim no originality over the specification data itself. The arrangement, Chinese annotations and practical notes on this page are written by us.
| Statement | Meaning | Example |
|---|---|---|
| SELECT | Select columns (* for all) | SELECT id, name FROM users |
| FROM | Specify source table | FROM orders |
| WHERE | Row filter condition | WHERE status = 'paid' |
| GROUP BY | Group rows for aggregation | GROUP BY category |
| HAVING | Filter grouped results | HAVING COUNT(*) > 1 |
| ORDER BY | Sort (ASC/DESC) | ORDER BY created_at DESC |
| LIMIT | Limit number of rows | LIMIT 10 |
| OFFSET | Skip first N rows (paging) | OFFSET 20 |
| DISTINCT | Return distinct values | SELECT DISTINCT city |
| JOIN | Inner join two tables | FROM a JOIN b ON a.id = b.a_id |
| LEFT JOIN | Left join, keep all left rows | FROM a LEFT JOIN b ON a.id = b.a_id |
| RIGHT JOIN | Right join, keep all right rows | FROM a RIGHT JOIN b ON a.id = b.a_id |
| INNER JOIN | Inner join (same as JOIN) | FROM a INNER JOIN b ON a.id = b.a_id |
| ON | Join condition | ON a.id = b.a_id |
| AS | Table or column alias | SELECT name AS full_name |
| INSERT INTO | Insert a row | INSERT INTO users (name) VALUES ('Tom') |
| UPDATE | Update rows | UPDATE users SET name = 'Tom' WHERE id = 1 |
| DELETE | Delete rows | DELETE FROM users WHERE id = 1 |
| CREATE TABLE | Create a table | CREATE TABLE t (id INT PRIMARY KEY) |
| ALTER TABLE | Alter table structure | ALTER TABLE t ADD COLUMN age INT |
| DROP TABLE | Drop a table | DROP TABLE t |
| PRIMARY KEY | Primary key constraint | id INT PRIMARY KEY |
| FOREIGN KEY | Foreign key constraint | FOREIGN KEY (a_id) REFERENCES a(id) |
| INDEX | Create index to speed up query | CREATE INDEX idx_name ON users(name) |
| UNION | Combine two result sets (distinct) | SELECT a FROM t1 UNION SELECT a FROM t2 |
| LIKE | Pattern match (% any, _ one) | WHERE name LIKE 'T%' |
| IN | Value in a set | WHERE status IN ('paid','done') |
| BETWEEN | Inclusive range | WHERE age BETWEEN 18 AND 30 |
| NULL / IS NULL | Null check (cannot use =) | WHERE deleted_at IS NULL |
| COUNT / SUM / AVG | Aggregate functions | SELECT COUNT(*) FROM users |
| CASE WHEN | Conditional expression | CASE WHEN age>18 THEN 'adult' ELSE 'minor' END |
| EXPLAIN | Show execution plan | EXPLAIN SELECT * FROM users |
Frequently Asked Questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows matching in both tables; LEFT JOIN returns all rows from the left table, filling right-side columns with NULL when unmatched. Use LEFT JOIN to keep every master record, INNER JOIN for the intersection.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can reference aggregates (e.g. COUNT(*)). You cannot use aggregate functions in WHERE.
What is the difference between % and _ in LIKE?
% matches any length (including zero) of characters; _ matches exactly one character. 'T%' matches any string starting with T; 'T_m' matches T, one char, then m (e.g. Tom).
How do I test for NULL?
NULL means unknown and cannot be tested with = NULL; use IS NULL / IS NOT NULL. In most databases comparing NULL to anything yields unknown (not true), which is a common trap.