🗄️ SQL

← Back to Cheatsheets

A quick-reference cheatsheet for the most commonly used SQL features. SQL is a declarative language for querying and manipulating relational databases. While dialects differ slightly between PostgreSQL, MySQL, and SQLite, the core syntax covered here works across all of them.

Paradigm: Declarative Type: Relational (DDL + DML) Dialects: PostgreSQL · MySQL · SQLite
Resources: PostgreSQL docs MySQL docs SQLite language reference SQLTutorial.org Use The Index, Luke — query performance

SELECT & Querying

▼

The SELECT statement is the foundation of SQL. Clauses are written in a specific order but executed in a different one: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Basic SELECT 
SELECT name, email
FROM users
WHERE active = true
ORDER BY name ASC
LIMIT 10;
Select All Columns 
SELECT * FROM users;
avoid in production — fetches unnecessary data and breaks if schema changes
Column Alias 
SELECT first_name || ' ' || last_name AS full_name
FROM users;
DISTINCT 
SELECT DISTINCT country FROM users;
removes duplicate rows from the result
LIMIT & OFFSET (Pagination) 
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;
OFFSET is slow on large tables — prefer cursor-based pagination

Filtering & WHERE

▼

The WHERE clause filters rows before grouping. Use HAVING to filter after aggregation. String comparisons are case-sensitive in PostgreSQL and case-insensitive in MySQL by default.

Comparison Operators 
= <> < > <= >=
<> and != are both "not equal"; != is not standard SQL
IN / NOT IN 
WHERE status IN ('active', 'pending')
WHERE id NOT IN (1, 2, 3)
BETWEEN 
WHERE age BETWEEN 18 AND 65
inclusive on both ends
LIKE / ILIKE 
WHERE name LIKE 'J%'
% = any sequence, _ = single char; ILIKE is case-insensitive, PostgreSQL only
NULL Checks 
WHERE email IS NULL
WHERE email IS NOT NULL
never use = NULL — it always evaluates to unknown
AND / OR / NOT 
WHERE active = true AND (role = 'admin' OR role = 'mod')
AND binds tighter than OR; use parentheses to be explicit
ORDER BY 
ORDER BY created_at DESC, name ASC
NULLs sort last in ASC by default in PostgreSQL; first in MySQL

JOINs

▼

Joins combine rows from two or more tables based on a related column. INNER JOIN returns only matching rows. LEFT JOIN keeps all rows from the left table, filling NULLs where there is no match on the right. Always join on indexed columns for performance.

INNER JOIN 
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
only rows with a match in both tables
LEFT JOIN 
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;
all users returned; order_count is 0 for users with no orders
Self Join 
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
Cross Join 
SELECT a.val, b.val
FROM table_a a CROSS JOIN table_b b;
cartesian product — every row paired with every other; use with care

Aggregation & GROUP BY

▼

Aggregate functions collapse multiple rows into a single value. Any column in the SELECT that is not inside an aggregate must appear in GROUP BY. HAVING filters groups after aggregation, where WHERE cannot reference aggregate results.

Common Aggregates 
COUNT(*) / COUNT(col) / SUM(col)
AVG(col) / MIN(col) / MAX(col)
COUNT(*) counts rows; COUNT(col) skips NULLs
GROUP BY + HAVING 
SELECT department,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

DDL – Schema

▼

DDL (Data Definition Language) statements create and modify the structure of tables. Prefer adding columns as nullable or with a default when altering live tables — adding a NOT NULL column without a default locks the table while it backfills on many engines.

CREATE TABLE 
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    name       TEXT NOT NULL,
    active     BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE 
ALTER TABLE users ADD COLUMN bio TEXT;
ALTER TABLE users DROP COLUMN bio;
ALTER TABLE users RENAME COLUMN bio TO about;
DROP TABLE 
DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;
CASCADE drops dependent objects like foreign keys

DML – Modifying Data

▼

Always include a WHERE clause on UPDATE and DELETE — omitting it affects every row in the table. Test with a SELECT using the same WHERE before running a destructive statement.

INSERT 
INSERT INTO users (name, email, created_at)
VALUES ('Alice', 'alice@example.com', NOW());

-- insert multiple rows
INSERT INTO tags (name) VALUES ('go'), ('python'), ('sql');
UPDATE 
UPDATE users
SET last_login = NOW(),
    login_count = login_count + 1
WHERE id = 42;
DELETE 
DELETE FROM sessions
WHERE expires_at < NOW();
UPSERT (PostgreSQL) 
INSERT INTO settings (user_id, key, value)
VALUES (1, 'theme', 'dark')
ON CONFLICT (user_id, key) DO UPDATE
    SET value = EXCLUDED.value;

Indexes & Performance

▼

Indexes speed up reads but slow down writes. Add them on columns frequently used in WHERE and JOIN clauses. Use EXPLAIN ANALYZE to diagnose slow queries and verify that indexes are being used.

Create Index 
CREATE INDEX idx_users_email ON users (email);
indexes speed up reads but slow down writes; add them on columns used in WHERE/JOIN
Unique Index 
CREATE UNIQUE INDEX idx_users_email ON users (email);
EXPLAIN / EXPLAIN ANALYZE 
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@b.com';
shows the query plan and actual execution time; use to diagnose slow queries

Window Functions

▼

Window functions perform calculations across a set of rows related to the current row without collapsing them like GROUP BY. Each row keeps its own result alongside the windowed calculation.

RANK() with PARTITION BY 
SELECT name, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
does not collapse rows like GROUP BY; each row keeps its own result

Common Patterns

▼

CTEs (Common Table Expressions) make complex queries readable by naming intermediate result sets. Subqueries can appear in SELECT, FROM, and WHERE clauses. Transactions group statements so they either all succeed or all fail together.

CTE (WITH Clause) 
WITH active_users AS (
    SELECT id, name FROM users WHERE active = true
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.name, COALESCE(o.cnt, 0) AS orders_last_30d
FROM active_users u
LEFT JOIN recent_orders o ON o.user_id = u.id;
Subquery 
SELECT name FROM users
WHERE id IN (
    SELECT DISTINCT user_id
    FROM orders WHERE total > 100
);
Transaction 
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or ROLLBACK; to undo