Statements, filtering, joins, aggregations and modern constructs across the common SQL dialects. All rows are plain text - search to filter, click Copy to grab the pattern.
Reference rack
STATUSREADY
ROWS45
NETWORKLOCAL
MODEREFERENCE
| Item / pattern | What it means / how to use it | |
|---|---|---|
| SELECT col1, col2 FROM t | Read specific columns from a table. | |
| SELECT DISTINCT col FROM t | Unique values only. | |
| SELECT * FROM t WHERE col = v | Filter rows by a condition. | |
| ORDER BY col DESC / ORDER BY col ASC | Sort results, usually null-last or null-first per dialect. | |
| LIMIT n / OFFSET m | Take n rows / skip m rows - the old-school pagination pair. | |
| UPDATE t SET col = v WHERE id = ? | Modify matching rows; ALWAYS keep the WHERE clause. | |
| DELETE FROM t WHERE id = ? | Delete matching rows; ALWAYS keep the WHERE clause. | |
| INSERT INTO t (a, b) VALUES (1, "x") | Insert one row with explicit columns. | |
| INSERT ... ON CONFLICT / ON DUPLICATE KEY | The upsert idioms of Postgres/SQLite and MySQL. | |
| MERGE INTO / UPSERT | Dialect-specific merge patterns for full synchronization. | |
| CREATE TABLE t (... ) | Define a table; add PRIMARY KEY, NOT NULL, UNIQUE, CHECK constraints. | |
| ALTER TABLE t ADD COLUMN col TYPE | Evolve a schema without dropping it. | |
| DROP TABLE t | Remove a table and its data - irreversible. | |
| WHERE col IN (1,2,3) | Membership test against a set. | |
| WHERE col LIKE "a%" | "%" matches any run of characters, "_" matches exactly one. | |
| WHERE col BETWEEN a AND b | Inclusive range check. | |
| WHERE col IS NULL / IS NOT NULL | Null checks - NULL never equals anything, not even NULL. | |
| AND / OR / NOT | Boolean combinators; use parentheses to control precedence. | |
| COUNT(*) / SUM(col) / AVG(col) / MIN(col) / MAX(col) | The core aggregate functions. | |
| GROUP BY col | Bucket rows; every selected non-aggregate column must appear here. | |
| HAVING COUNT(*) > 5 | Filter on aggregated results after grouping. | |
| COUNT(DISTINCT col) | Distinct-value count. | |
| INNER JOIN b ON a.id = b.aid | Rows that match on both sides. | |
| LEFT JOIN b ON a.id = b.aid | All left rows, NULLs wherever the right side is missing. | |
| RIGHT JOIN / FULL OUTER JOIN | Right-side or both-sides completeness. | |
| CROSS JOIN | Cartesian product - watch for row explosion. | |
| Self-join | Join a table to itself with aliases for trees and hierarchies. | |
| UNION / UNION ALL | Combine result sets; ALL keeps duplicates. | |
| WHERE col = (SELECT MAX(x) FROM t) | A scalar subquery. | |
| EXISTS (SELECT 1 ... ) | A cheap presence/absence test that stops at the first hit. | |
| WITH cte AS (SELECT ... ) | A named subquery you can reference multiple times. | |
| WITH RECURSIVE cte AS (... ) | Recursive CTEs for trees and graphs like org charts. | |
| CASE WHEN cond THEN a ELSE b END | Conditional value per row. | |
| COALESCE(a, b, 0) | The first non-NULL argument. | |
| CAST(x AS type) / x::type | Explicit type coercion. | |
| DATE_TRUNC("day", ts) / EXTRACT(YEAR FROM ts) | Truncate timestamps or pull parts out. | |
| ROW_NUMBER() OVER (PARTITION BY g ORDER BY ...) | Number rows within each partition. | |
| SUM(x) OVER (PARTITION BY g) | Window aggregate - running totals without collapsing rows. | |
| DISTINCT ON (Postgres) | First row per group, honoring your ORDER BY. | |
| JSON extract / ->> and jsonb columns | Query JSON documents stored in SQL columns. | |
| CREATE INDEX idx ON t (col) | Speed up lookups on that column; every insert pays a small write cost. | |
| EXPLAIN / EXPLAIN ANALYZE | The query plan and real timings - step one of any slow-query hunt. | |
| PRAGMA table_info(t) (SQLite) | Introspect a SQLite table's schema. | |
| STRFTIME / DATE_FORMAT | SQLite / MySQL date formatting functions. | |
| BEGIN / COMMIT / ROLLBACK | Wrap multiple statements in one atomic transaction. |