SQL Query Cheat Sheet: Essential Queries and Patterns

      Each section includes the syntax, a practical example, and notes on database-specific
      differences where they matter. Bookmark this page and use it as your daily reference
      for writing SQL queries.

      The foundation of every SQL query. `SELECT` specifies
      which columns to return, `FROM` specifies the table.

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Select with alias
SELECT u.id, u.name AS username FROM users AS u;

-- Select distinct values
SELECT DISTINCT status FROM orders;

-- Select with LIMIT (MySQL/Postgres)
SELECT * FROM products LIMIT 10;

-- Select with TOP (SQL Server)
SELECT TOP 10 * FROM products;

-- Select with FETCH (standard SQL / Oracle)
SELECT * FROM products OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

      Filter rows based on conditions. The WHERE clause is evaluated row-by-row before
      GROUP BY and aggregate functions.

-- Comparison operators
SELECT * FROM users WHERE age >= 18;
SELECT * FROM orders WHERE total != 0;

-- String matching
SELECT * FROM users WHERE name LIKE 'A%';    -- starts with A
SELECT * FROM users WHERE name LIKE '%son%';  -- contains 'son'
SELECT * FROM users WHERE name LIKE '_oe';    -- exactly 3 chars, ends with 'oe'

-- IN operator
SELECT * FROM orders WHERE status IN ('shipped', 'delivered', 'pending');

-- BETWEEN
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- NULL checks
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

-- Multiple conditions
SELECT * FROM orders
WHERE status = 'active'
  AND created_at >= '2024-01-01'
  AND (total > 100 OR priority = 'high');

-- Boolean expressions
SELECT * FROM products
WHERE NOT discontinued
  AND (category_id = 5 OR category_id = 8);

      Combine rows from two or more tables based on related columns. Understanding JOINs
      is critical for working with normalized databases.

    Returns rows where there is a match in **both** tables.

SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Only returns users with at least one order

    Returns all rows from the **left** table, with matching rows from the right table. Non-matching right-side columns are `NULL`.

SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Returns ALL users, even if they have no orders

    Returns all rows from the **right** table, with matching rows from the left table. Less common — usually you can rewrite as a LEFT JOIN by swapping table order.

SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- All orders, even if user is missing

    Returns all rows from **both** tables. Missing matches are `NULL` on the opposite side.

SELECT u.name, o.id AS order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- All users and all orders, matched where possible

    Cartesian product — every row from table A combined with every row from table B. Use sparingly.

SELECT sizes.name, colors.name
FROM sizes
CROSS JOIN colors;
-- Returns size × color combinations

    Joining a table to itself — useful for hierarchies (e.g., employees and managers).

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

      `GROUP BY` groups rows with the same values, then
      aggregate functions are applied per group. `HAVING`
      filters groups (like WHERE filters rows).

-- Count users per status
SELECT status, COUNT(*) AS user_count
FROM users
GROUP BY status;

-- Multiple grouping columns
SELECT category_id, YEAR(created_at) AS year, COUNT(*) AS count
FROM products
GROUP BY category_id, YEAR(created_at);

-- Using HAVING (filter groups)
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING AVG(price) > 50;

-- HAVING with multiple conditions
SELECT user_id, COUNT(*) AS order_count, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 5 AND SUM(total) > 1000
ORDER BY total_spent DESC;

    Sort results by one or more columns. Sorting is applied last, after GROUP BY and HAVING.

-- Ascending (default)
SELECT name, price FROM products ORDER BY price;

-- Descending
SELECT name, price FROM products ORDER BY price DESC;

-- Multiple sort keys
SELECT name, category_id, price
FROM products
ORDER BY category_id ASC, price DESC;

-- Sort by aggregate alias
SELECT category_id, COUNT(*) AS cnt
FROM products
GROUP BY category_id
ORDER BY cnt DESC;

-- Sort by column position (not recommended)
SELECT name, price FROM products ORDER BY 2 DESC;

      Subqueries (nested queries) can appear in SELECT, FROM, WHERE, or HAVING clauses.
      They let you break complex problems into logical steps.

-- Subquery in WHERE
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- Subquery with IN
SELECT name, email
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 500);

-- Subquery in SELECT (scalar subquery)
SELECT
  u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- Subquery in FROM (derived table / subquery as table)
SELECT category, AVG(price) AS avg_price
FROM (
  SELECT p.*, c.name AS category
  FROM products p
  JOIN categories c ON p.category_id = c.id
) AS product_categories
GROUP BY category;

-- Correlated subquery (references outer query)
SELECT p1.name, p1.price, p1.category_id
FROM products p1
WHERE p1.price = (
  SELECT MAX(p2.price)
  FROM products p2
  WHERE p2.category_id = p1.category_id
);
-- Gets the most expensive product in each category

      Window functions perform calculations across a set of rows related to the current row
      — **without collapsing** the result set like GROUP BY
      does. They are among the most powerful SQL features for analytics.

-- ROW_NUMBER: unique sequential number per partition
SELECT
  name,
  salary,
  department_id,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;
-- Within each department, rank employees by salary descending

-- RANK: same values get same rank, next skips numbers
SELECT
  name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
-- 3rd place if 2 tied: 100, 95, 95, 90 → ranks 1, 2, 2, 4

-- DENSE_RANK: no gaps in ranking
SELECT
  name, salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;
-- 100, 95, 95, 90 → ranks 1, 2, 2, 3

    Access data from previous or next rows without self-joins.

-- LAG: previous row value
SELECT
  date,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue,
  LAG(revenue, 7) OVER (ORDER BY date) AS prev_week_revenue
FROM daily_revenue;

-- LEAD: next row value
SELECT
  date,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY date) AS next_day_revenue
FROM daily_revenue;

-- Difference from previous value
SELECT
  employee_id,
  salary,
  salary - LAG(salary, 1, 0) OVER (ORDER BY employee_id) AS salary_change
FROM salaries;

-- Running total
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

-- Moving average (3-day)
SELECT
  date,
  revenue,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day
FROM daily_revenue;

-- Partitioned aggregates
SELECT
  department_id,
  employee_name,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
  salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_dept_avg
FROM employees;

-- Single row
INSERT INTO users (name, email, status)
VALUES ('Alice', '[email protected]', 'active');

-- Multiple rows
INSERT INTO products (name, price, category_id)
VALUES
  ('Widget', 9.99, 1),
  ('Gadget', 24.99, 1),
  ('Doohickey', 14.99, 2);

-- Insert from query
INSERT INTO archived_orders (id, user_id, total, created_at)
SELECT id, user_id, total, created_at
FROM orders
WHERE created_at < '2023-01-01';

-- Simple update
UPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01';

-- Update multiple columns
UPDATE products
SET
  price = price * 1.10,
  updated_at = NOW()
WHERE category_id = 5;

-- Update with subquery
UPDATE products
SET category_id = (
  SELECT id FROM categories WHERE name = 'Clearance'
)
WHERE category_id IS NULL;

-- Delete specific rows
DELETE FROM logs WHERE created_at < '2020-01-01';

-- Delete all rows (truncate is faster)
DELETE FROM temp_data;

-- Delete with subquery
DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders);

-- Basic table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with composite primary key
CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

      Indexes speed up queries at the cost of slower writes. Use them on columns used
      in WHERE, JOIN, and ORDER BY.

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Unique index
CREATE UNIQUE INDEX idx_products_sku ON products(sku);

-- Partial index (Postgres)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- Full-text index (MySQL/Postgres)
CREATE INDEX idx_products_name ON products USING GIN(to_tsvector('english', name));

-- Drop index
DROP INDEX idx_users_email;

      CTEs let you name a subquery and reference it like a table. They improve readability
      and enable recursive queries.

-- Basic CTE
WITH high_value_orders AS (
  SELECT user_id, SUM(total) AS total_spent
  FROM orders
  WHERE status = 'delivered'
  GROUP BY user_id
  HAVING SUM(total) > 1000
)
SELECT u.name, h.total_spent
FROM users u
JOIN high_value_orders h ON u.id = h.user_id;

-- Recursive CTE (hierarchy)
WITH RECURSIVE org_chart AS (
  -- Base: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: direct reports
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

      These five mistakes cause most of the slow queries and wrong results

      I've debugged in production. Each one has a simple fix once you

      know what to look for.

  -
    **Comparing to NULL with `=`.**
    `WHERE email = NULL` returns zero rows
    because NULL comparisons evaluate to UNKNOWN, not TRUE. Always use
    `IS NULL` / `IS NOT NULL`
    — and remember that `NULL <> 'x'`
    also excludes NULL rows.


  -
    **Using `SELECT *` in production code.**
    It pulls every column, wasting I/O and bandwidth, and silently breaks when
    someone adds a column. List columns explicitly — it also makes your
    application's data contract visible in the query itself.


  -
    **The N+1 query trap.**
    Fetching a list of users, then running one query per user inside a loop,
    is the classic ORM performance killer. Replace it with a single JOIN or an
    `IN` subquery:
    `SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE plan = 'pro')`.


  -
    **Wrapping indexed columns in functions.**
    `WHERE YEAR(created_at) = 2024` forces a full
    scan because the index stores raw values, not function results. Use a range
    predicate instead:
    `created_at >= '2024-01-01' AND created_at < '2025-01-01'`.


  -
    **Deep OFFSET pagination.**
    `OFFSET 100000 LIMIT 50` still reads and
    discards 100,000 rows. Use keyset pagination —
    `WHERE id > last_seen_id ORDER BY id LIMIT 50`
    — which stays fast no matter how deep you go.