A quick reference for SQL syntax. Covers SELECT queries, filtering, joins, aggregation, subqueries, window functions, indexes, and data modification statements. Syntax is standard SQL with dialect notes where PostgreSQL, MySQL, and SQL Server diverge — paste queries into the SQL formatter to tidy them up.
SELECT & Filtering
| Syntax | Description |
|---|---|
| SELECT * FROM t | Select all columns from table |
| SELECT a, b FROM t | Select specific columns |
| SELECT DISTINCT a FROM t | Select unique values only |
| WHERE a = 1 | Filter by exact match |
| WHERE a IN (1, 2, 3) | Filter by value list |
| WHERE a BETWEEN 1 AND 10 | Filter by range (inclusive) |
| WHERE a LIKE 'foo%' | Pattern match (% = any chars) — single quotes; double quotes mean identifiers |
| WHERE a IS NULL | Check for null values |
| WHERE a AND (b OR c) | Combine conditions |
ORDER BY & LIMIT
| Syntax | Description |
|---|---|
| ORDER BY col ASC | Sort ascending (default) |
| ORDER BY col DESC | Sort descending |
| ORDER BY a, b DESC | Sort by multiple columns |
| LIMIT 10 | Return first 10 rows |
| LIMIT 10 OFFSET 20 | Skip 20 rows, then return 10 |
| ORDER BY col NULLS LAST | Place nulls at end (PostgreSQL) |
JOINs
| Syntax | Description |
|---|---|
| INNER JOIN t2 ON t1.id = t2.id | Rows matching in both tables |
| LEFT JOIN t2 ON t1.id = t2.id | All from left, matching from right |
| RIGHT JOIN t2 ON t1.id = t2.id | All from right, matching from left |
| FULL OUTER JOIN t2 ON .. | All rows from both tables |
| CROSS JOIN t2 | Cartesian product of both tables |
| t1 JOIN t2 USING (col) | Join on identically named column |
| t1 NATURAL JOIN t2 | Auto-join on all shared column names — avoid; breaks when columns are added |
| LEFT JOIN t2 ON .. WHERE t2.id IS NULL | Rows in t1 not in t2 (anti-join) |
GROUP BY & Aggregation
| Syntax | Description |
|---|---|
| GROUP BY col | Group rows by column value |
| COUNT(*) | Count all rows in group |
| COUNT(DISTINCT col) | Count unique values |
| SUM(col) | Sum of values in group |
| AVG(col) | Average of values in group |
| MIN(col) / MAX(col) | Minimum / maximum value |
| HAVING COUNT(*) > 1 | Filter groups after aggregation |
| GROUP BY ROLLUP(a, b) | Subtotals and grand total |
Subqueries
| Pattern | Description |
|---|---|
| WHERE col IN (SELECT ..) | Filter by subquery result set |
| WHERE EXISTS (SELECT ..) | True if subquery returns rows |
| WHERE col > (SELECT AVG(..)) | Compare to scalar subquery |
| SELECT (SELECT ..) AS sub | Scalar subquery in select list |
| FROM (SELECT ..) AS t | Derived table (inline view) |
| WITH cte AS (SELECT ..) SELECT .. | Common Table Expression (CTE) |
| WITH RECURSIVE cte AS (..) | Recursive CTE for hierarchies |
Window Functions
| Syntax | Description |
|---|---|
| ROW_NUMBER() OVER (ORDER BY col) | Sequential row number |
| RANK() OVER (ORDER BY col) | Rank with gaps for ties |
| DENSE_RANK() OVER (ORDER BY col) | Rank without gaps |
| LAG(col, 1) OVER (ORDER BY col) | Value from previous row |
| LEAD(col, 1) OVER (ORDER BY col) | Value from next row |
| SUM(col) OVER (PARTITION BY grp) | Running total per partition |
| NTILE(4) OVER (ORDER BY col) | Divide rows into N buckets |
| OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | Cumulative frame |
Indexes
| Syntax | Description |
|---|---|
| CREATE INDEX idx ON t(col) | Create B-tree index on column |
| CREATE UNIQUE INDEX idx ON t(col) | Index enforcing uniqueness |
| CREATE INDEX idx ON t(a, b) | Composite index (multi-column) |
| DROP INDEX idx | Remove an index |
| CREATE INDEX CONCURRENTLY .. | Non-blocking index creation (PostgreSQL) |
| EXPLAIN SELECT .. | Show query execution plan |
INSERT / UPDATE / DELETE
| Syntax | Description |
|---|---|
| INSERT INTO t (a, b) VALUES (1, 2) | Insert a single row |
| INSERT INTO t SELECT .. FROM t2 | Insert from another query |
| UPDATE t SET a = 1 WHERE b = 2 | Update matching rows |
| DELETE FROM t WHERE a = 1 | Delete matching rows |
| TRUNCATE TABLE t | Delete all rows — fast; resets identity in MySQL/SQL Server (Postgres needs RESTART IDENTITY) |
| INSERT .. ON CONFLICT DO UPDATE .. | Upsert (PostgreSQL) |
| INSERT .. ON DUPLICATE KEY UPDATE .. | Upsert (MySQL) |
| MERGE INTO t USING .. | Upsert (SQL Server / Oracle, PostgreSQL 15+) |
Common gotchas
NULLuses three-valued logic:a = NULLis never true — useIS NULL. Worse,NOT IN (subquery)returns zero rows if the subquery contains a single NULL. UseNOT EXISTSinstead.WHEREruns before grouping,HAVINGafter — filtering on an aggregate in WHERE is a syntax error, and column aliases from SELECT are not visible in WHERE in most dialects.- Row limits are dialect-specific:
LIMIT(PostgreSQL/MySQL/SQLite),TOP n(SQL Server),FETCH FIRST n ROWS ONLY(standard SQL:2008, Oracle 12c+). Same story for upserts — see the INSERT table above. UPDATEandDELETEwithoutWHEREhit every row, silently. Run the SELECT version first, or wrap in a transaction you can roll back.COUNT(col)skips NULLs;COUNT(*)counts rows. The two differ the moment the column is nullable.
Dialect deep dives: PostgreSQL cheat sheet (18 added uuidv7() and OLD/NEW in RETURNING), MySQL cheat sheet, and the PostgreSQL vs MongoDB guide.