A quick reference for SQL syntax. Covers SELECT queries, filtering, joins, aggregation, subqueries, window functions, indexes, and data modification statements.
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) |
| 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 |
| 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, no log) |
| INSERT .. ON CONFLICT DO UPDATE .. | Upsert (PostgreSQL) |
| INSERT .. ON DUPLICATE KEY UPDATE .. | Upsert (MySQL) |
| MERGE INTO t USING .. | Upsert (SQL Server / Oracle) |