SELECT, JOINs, window functions, CTEs, indexes, JSON — with copy-paste examples
SELECT * FROM users;SELECT name, email FROM users;SELECT DISTINCT country FROM users;; terminates statement. * returns all columns. List specific columns for better performance.SELECT * FROM users WHERE age >= 18;SELECT * FROM orders WHERE status = 'pending';SELECT * FROM products WHERE price BETWEEN 10 AND 100;SELECT * FROM logs WHERE created_at >= NOW() - INTERVAL '7 days';=, != or <>, <, >, IN, LIKE (% wildcard), IS NULL, IS NOT NULL.SELECT * FROM users ORDER BY created_at DESC;ORDER BY name ASC, age DESC (multi-column)
SELECT * FROM posts LIMIT 10;LIMIT 20 OFFSET 40 -- page 3, 20 per pageLIMIT 20 ROWS OFFSET 40 -- ANSI SQL
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;INNER JOIN returns only rows with matches in both tables. JOIN keyword alone = INNER JOIN.SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;LEFT JOIN returns all rows from left table. Unmatched right columns are NULL. Use to find orphans: add WHERE o.id IS NULL.SELECT COALESCE(u.name, 'Unknown'), o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;FULL OUTER JOIN returns all rows from both tables. Unmatched sides are NULL. COALESCE() replaces NULL with fallback.SELECT c.name, p.name
FROM colors c CROSS JOIN products p;-- Equivalent to:
SELECT * FROM table1, table2;SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
SELECT * FROM table1 NATURAL JOIN table2;ON.
SELECT country, COUNT(*) AS cnt
FROM users
GROUP BY country;SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;WHERE filters rows before grouping. HAVING filters groups after aggregation. Non-aggregated columns must appear in GROUP BY.COUNT(*) — row countCOUNT(col) — non-null countSUM(col) — totalAVG(col) — average
MIN(col) — minimumMAX(col) — maximumSTRING_AGG(col, ', ') — PostgreSQL string concatARRAY_AGG(col) — collect into array
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;PARTITION BY splits rows into partitions. Window functions restart for each partition.ROW_NUMBER() — unique sequentialRANK() — standard ranking (gaps)DENSE_RANK() — no gapsNTILE(4) — quartiles
LAG(col, 1) — previous rowLEAD(col, 1) — next rowFIRST_VALUE(col)LAST_VALUE(col)
SUM() OVER ()AVG() OVER ()COUNT() OVER ()MIN()/MAX() OVER ()
WITH active_users AS (
SELECT * FROM users WHERE active = true
), recent_orders AS (
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT u.name, COUNT(o.id) AS order_count
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id
GROUP BY u.name;INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');INSERT INTO users (name) VALUES ('Bob'), ('Charlie'), ('Dana'); -- multi-rowINSERT INTO users SELECT * FROM staging_users; -- from querySERIAL columns (auto-increment) can be omitted.UPDATE users SET active = true WHERE last_login > NOW() - INTERVAL '30 days';UPDATE products SET price = price * 1.1; -- increase all by 10%UPDATE users SET meta = jsonb_set(meta, '{theme}', '"dark"') WHERE id = 1;WHERE unless you truly mean to update every row. WHERE missing = full table update.DELETE FROM users WHERE last_login < NOW() - INTERVAL '1 year';DELETE FROM sessions WHERE user_id = 123;DELETE FROM table_name without WHERE removes every row (but keeps table structure). Use TRUNCATE for faster full-table clear.TRUNCATE TABLE logs;TRUNCATE TABLE users, orders RESTART IDENTITY CASCADE;TRUNCATE is DDL, not DML — it's fast (doesn't row-by-row delete), resets identity columns by default in PG. RESTART IDENTITY resets auto-increment. CASCADE truncates dependent tables.CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);SERIAL auto-increment integer in PG. VARCHAR(n) variable string with limit. TIMESTAMP with time zone = TIMESTAMPTZ. DEFAULT values set on insert.ALTER TABLE users ADD COLUMN age INT;ALTER TABLE users DROP COLUMN middle_name;ALTER TABLE users RENAME TO app_users;ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);ALTER commands are transactional in PG — you can ROLLBACK an ALTER TABLE.CREATE INDEX idx_email ON users(email);CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_last_first ON users(last_name, first_name);CREATE INDEX idx_active_users ON users(email) WHERE active = true;
DROP INDEX idx_email;SELECT * FROM pg_indexes WHERE tablename = 'users';
WHERE, JOIN ON, and ORDER BY columns. Partial indexes are efficient for subset queries (e.g., only active users).-- Column type
CREATE TABLE users (
meta JSONB
);-- Insert JSON
INSERT INTO users (meta) VALUES ('{"theme": "dark", "notifications": true}');-- Query jsonb field
SELECT meta->>'theme' AS theme FROM users;-- Check contains
SELECT * FROM users WHERE meta @> '{"theme": "dark"}';-- Update field
UPDATE users SET meta = jsonb_set(meta, '{theme}', '"light"') WHERE id = 1;-> returns JSON object, ->> returns text. @> contains operator. jsonb_set() updates nested path.
Use GIN indexes on JSONB for performance: CREATE INDEX idx_meta ON users USING GIN (meta);
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]')
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, email = EXCLUDED.email;-- Ignore duplicates
INSERT ... ON CONFLICT DO NOTHING;EXCLUDED is the row that would have been inserted. ON CONFLICT (column) needs a UNIQUE or PRIMARY KEY constraint on that column.INSERT INTO users (name) VALUES ('Bob') RETURNING id;UPDATE users SET active = false WHERE last_login < '2020-01-01' RETURNING id, email;DELETE FROM sessions WHERE expired = true RETURNING COUNT(*);RETURNING works with INSERT, UPDATE, DELETE, and even MERGE. Saves a second query. Returns actual values (including defaults, triggers).WITH RECURSIVE subtree AS (
SELECT id, parent_id, name FROM categories WHERE id = 5
UNION ALL
SELECT c.id, c.parent_id, c.name
FROM categories c
INNER JOIN subtree s ON c.parent_id = s.id
)
SELECT * FROM subtree;
WHERE NOT EXISTS or max depth to prevent infinite loops.CREATE INDEX ... — normalCREATE INDEX ... ON table USING HASH (col);CREATE INDEX ... ON table USING GIN (jsonb_col);tsvector
CREATE INDEX ... ON table USING GiST (point_col);CREATE INDEX ... ON table USING BRIN (created_at);As we navigate the data-driven world of 2026, Structured Query Language (SQL) remains the bedrock of modern application development. While new database technologies emerge, the ability to efficiently query, manipulate, and manage relational data is a fundamental skill for every developer and data analyst. Whether you're building a high-performance backend, generating complex business reports, or optimizing database schemas, having a quick and reliable SQL reference is essential. Our SQL Cheat Sheet provides a categorized, searchable guide to the most common statements, joins, and functions, helping you write cleaner and more efficient queries.
A major focus in 2026 is the role of "Query Optimization" and "Data Integrity." Modern applications demand sub-second response times and rock-solid reliability. Our cheat sheet includes the essential syntax for indexing (CREATE INDEX), complex joins (INNER, LEFT, RIGHT, FULL), and transaction management (BEGIN, COMMIT, ROLLBACK), ensuring your data operations are both fast and secure. By mastering these core utilities, you can build applications that scale gracefully and maintain high performance as your datasets grow.
Finally, consider the importance of "Local-First" privacy in your development environment. At AllOmnitools, we believe your database structures and query habits should remain private. Our SQL Cheat Sheet runs entirely in your browser, ensuring that your search queries and the snippets you browse never leave your device. This approach provides zero-latency access to critical information, even in restricted or offline environments. By combining this cheat sheet with other AllOmnitools like the SQL Formatter, you have a complete ecosystem for mastering modern data management in 2026.
Zero server lag. All SQL references and query examples are available instantly for your workflow.
Your search queries and development preferences remain private. We never track or store your data management habits.
INNER JOIN returns only the rows where there is a match in both tables. LEFT JOIN returns all rows from the left table, and the matched rows from the right table (filling with NULL if no match exists).
Always use Prepared Statements and Parameterized Queries. Never concatenate user input directly into your SQL strings. Modern ORMs and database drivers handle this for you automatically.
An index is a data structure that improves the speed of data retrieval operations on a table. Think of it like an index in a book→it allows the database to find rows much faster without scanning the entire table.
Yes. AllOmnitools is "local-first." We provide the information and code snippets locally in your browser. You never need to submit your proprietary database schemas to our servers.
Our cheat sheet focuses on standard ANSI SQL, which is compatible with PostgreSQL, MySQL, SQL Server, and SQLite. Most commands work across all major relational databases.
The standard way is to use a Common Table Expression (CTE) with ROW_NUMBER() to identify duplicates and then delete rows where the rank is greater than 1.