🗄️ 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.

Resources

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 & Sorting

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

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;

• Window function:

  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)

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;

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;

• Indexes:
  CREATE INDEX idx_users_email ON users (email);
CREATE UNIQUE INDEX idx_users_email ON users (email);
  (indexes speed up reads but slow down writes; add them on columns used in WHERE/JOIN)

• Drop table:
  DROP TABLE users;
DROP TABLE IF EXISTS users CASCADE;
  (CASCADE drops dependent objects like foreign keys)

Advanced

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

• 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)