TLDR reference

SQL cheatsheet

A searchable reference for the SQL you write most - joins, filtering, aggregation, and common query patterns - each with a copyable snippet. Type to search, or filter by group. Everything runs in your browser.

• Select specific columns

  Querying

  SELECT name, email FROM users;

  Returns just the columns you name. Prefer this over SELECT * so the result is stable and lean.

• Sort the result

  Querying

  SELECT name, created_at
  FROM users
  ORDER BY created_at DESC;

  ORDER BY sorts rows; DESC reverses to newest-first. Add more columns for tie-breakers.

• Limit how many rows

  Querying

  SELECT *
  FROM users
  ORDER BY id
  LIMIT 10 OFFSET 20;

  LIMIT caps the row count; OFFSET skips ahead, the basis of pagination. SQL Server uses TOP / FETCH.

• Rename a column or table

  Querying

  SELECT name AS full_name FROM users AS u;

  AS gives a column or table an alias, handy for readable output and shorter join conditions.

• Remove duplicate rows

  Querying

  SELECT DISTINCT country FROM users;

  DISTINCT collapses identical rows so each country appears once.

• Filter rows

  Filtering

  SELECT *
  FROM orders
  WHERE total > 100;

  WHERE keeps only rows that satisfy the condition. It runs before grouping and sorting.

• Combine conditions

  Filtering

  SELECT *
  FROM users
  WHERE active = true AND country = 'CZ';

  Chain conditions with AND / OR; parenthesise to control precedence.

• Match a set of values

  Filtering

  SELECT *
  FROM orders
  WHERE status IN ('paid', 'shipped');

  IN tests membership of a list - cleaner than a chain of OR comparisons.

• Match a range

  Filtering

  SELECT *
  FROM orders
  WHERE created_at BETWEEN '2026-01-01' AND '2026-06-30';

  BETWEEN is inclusive on both ends. Works for numbers and dates alike.

• Pattern match text

  Filtering

  SELECT *
  FROM users
  WHERE email LIKE '%@gmail.com';

  LIKE matches patterns: % is any run of characters, _ is a single one. ILIKE is case-insensitive in Postgres.

• Find rows with no value

  Filtering

  SELECT *
  FROM users
  WHERE deleted_at IS NULL;

  NULL is unknown, so test it with IS NULL / IS NOT NULL - never with = NULL, which is never true.

• Keep only matching rows (INNER JOIN)

  Joins

  SELECT o.id, u.name
  FROM orders o
  INNER JOIN users u ON u.id = o.user_id;

  Returns rows that have a match in both tables. Orders without a user, or users without an order, drop out.

• Keep all left rows (LEFT JOIN)

  Joins

  SELECT u.name, o.id
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id;

  Returns every user, with NULLs where they have no order. The way to find users who never ordered.

• Keep all right rows (RIGHT JOIN)

  Joins

  SELECT u.name, o.id
  FROM users u
  RIGHT JOIN orders o ON o.user_id = u.id;

  The mirror of LEFT JOIN: every order, with NULLs where the user is missing. Rare; usually rewritten as a LEFT JOIN.

• Keep unmatched from both (FULL OUTER JOIN)

  Joins

  SELECT u.name, o.id
  FROM users u
  FULL OUTER JOIN orders o ON o.user_id = u.id;

  Returns matched rows plus unmatched rows from each side, padded with NULLs. MySQL lacks it; emulate with UNION.

• Find rows with no match

  Joins

  SELECT u.name
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  WHERE o.id IS NULL;

  A LEFT JOIN then a NULL check on the right key is the classic "users with no orders" anti-join.

• Count rows

  Aggregation

  SELECT COUNT(*) FROM users;

  COUNT(*) counts all rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts unique ones.

• Sum, average, min, max

  Aggregation

  SELECT SUM(total), AVG(total), MIN(total), MAX(total) FROM orders;

  The core aggregate functions. They ignore NULLs and collapse the whole table to one row.

• Aggregate per group

  Aggregation

  SELECT user_id, COUNT(*) AS orders
  FROM orders
  GROUP BY user_id;

  GROUP BY produces one row per group; every selected column must be grouped or aggregated.

• Filter groups

  Aggregation

  SELECT user_id, COUNT(*) AS orders
  FROM orders
  GROUP BY user_id
  HAVING COUNT(*) > 5;

  HAVING filters after aggregation, where WHERE cannot reach. Use it to test aggregate results.

• Subquery in WHERE

  Subqueries & CTEs

  SELECT *
  FROM users
  WHERE id IN (SELECT user_id FROM orders);

  A subquery feeds a value or list to the outer query - here, users who have at least one order.

• Common table expression (CTE)

  Subqueries & CTEs

  WITH recent AS (
    SELECT * FROM orders WHERE created_at > '2026-01-01'
  )
  SELECT user_id, COUNT(*) FROM recent GROUP BY user_id;

  WITH names a subquery so you can reuse it and read top-to-bottom instead of nesting.

• EXISTS check

  Subqueries & CTEs

  SELECT *
  FROM users u
  WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

  EXISTS stops at the first matching row, often faster than IN for a correlated check.

• Rank rows

  Window functions

  SELECT name, total,
    RANK() OVER (ORDER BY total DESC) AS rank
  FROM orders;

  A window function computes across a set of rows without collapsing them. RANK leaves gaps after ties.

• One row per group (deduplicate)

  Window functions

  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
  ) t
  WHERE rn = 1;

  ROW_NUMBER per partition keeps the latest order per user - the standard deduplication pattern.

• Running total

  Window functions

  SELECT created_at, total,
    SUM(total) OVER (ORDER BY created_at) AS running
  FROM orders;

  A windowed SUM with ORDER BY accumulates a running total row by row.

• Insert rows

  Modifying data

  INSERT INTO users (name, email)
  VALUES ('Ada', 'ada@example.com');

  Adds a row. List multiple parenthesised tuples to insert several at once.

• Update rows

  Modifying data

  UPDATE users
  SET active = false
  WHERE last_login < '2025-01-01';

  Changes existing rows. Always include a WHERE clause - without one, every row is updated.

• Delete rows

  Modifying data

  DELETE FROM users
  WHERE active = false;

  Removes rows. Like UPDATE, a missing WHERE clause wipes the whole table.

• Insert or update (upsert)

  Modifying data

  INSERT INTO settings (key, value)
  VALUES ('theme', 'dark')
  ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

  Upsert inserts, or updates if the key already exists. MySQL spells it ON DUPLICATE KEY UPDATE.

• Create a table

  Schema

  CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT now()
  );

  Defines a table with column types and constraints. SERIAL is Postgres auto-increment; MySQL uses AUTO_INCREMENT.

• Add a column

  Schema

  ALTER TABLE users ADD COLUMN age INT;

  ALTER TABLE changes an existing table - add, drop, or rename columns and constraints.

• Create an index

  Schema

  CREATE INDEX idx_users_email ON users (email);

  An index speeds up lookups and joins on a column at the cost of slightly slower writes.

No patterns match your search.