SQL Cheat Sheet for Interviews and Real Projects (JOINs, Windows and Traps)

SQL Cheat Sheet for Interviews and Real Projects

SQL appears in far more job interviews than candidates expect — including for frontend, product, and analyst roles that never mention a database in the job description.

It is also the rare skill that transfers completely. SQL learned on PostgreSQL works on MySQL, SQLite, SQL Server, and BigQuery with only minor dialect differences. Learn it once, use it for a career.

This sheet covers the syntax you look up, the JOIN semantics people get wrong, and the traps that produce results which look correct and are not.

Updated for 2026. Standard SQL; dialect differences noted where they matter.

Query Order of Execution

Learn this before anything else. SQL is written in one order and executed in another, and that mismatch explains most confusing SQL errors.

Two consequences follow directly, and both are frequent interview questions:

You cannot use a SELECT alias in WHERE. WHERE runs at step 2; the alias is created at step 5 and does not exist yet. You can use it in ORDER BY, which runs at step 7.

You cannot use an aggregate in WHERE. COUNT, SUM and friends are produced by GROUP BY at step 3. Filtering on them is what HAVING exists for.

The Basics

SELECT name, email FROM users;
SELECT * FROM users WHERE age > 18;
SELECT DISTINCT country FROM users;

SELECT * FROM users
WHERE country = 'US' AND age BETWEEN 18 AND 65
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

-- comparison and matching
WHERE age >= 18
WHERE name LIKE 'A%'          -- starts with A
WHERE name ILIKE 'a%'         -- case-insensitive (PostgreSQL)
WHERE id IN (1, 2, 3)
WHERE email IS NULL           -- never  = NULL
WHERE deleted_at IS NOT NULL

IS NULL, never = NULL. NULL means "unknown", and comparing an unknown to anything — including another unknown — produces unknown, not true. WHERE email = NULL returns zero rows, always, silently. This is the single most common beginner SQL bug.

JOINs — The Section That Matters Most

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Think of it as a question:

• "Customers who have ordered" → INNER JOIN
• "All customers, and their orders if any" → LEFT JOIN

Most reporting bugs where a total comes out too low are an INNER JOIN that should have been a LEFT JOIN. The rows did not error — they silently vanished.

The LEFT JOIN trap

This is worth its own section because it produces wrong answers that nobody notices for months.

-- BROKEN — this is secretly an INNER JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

-- CORRECT
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
                  AND o.status = 'shipped';

The LEFT JOIN correctly produces NULL in every orders column for customers with no orders. Then WHERE o.status = 'shipped' evaluates NULL = 'shipped', which is unknown, which is not true — and those rows are removed. You have written a LEFT JOIN and received an INNER JOIN.

The rule: conditions about what counts as a match go in ON. Conditions that filter the final result go in WHERE.

Aggregation

SELECT country, COUNT(*) AS user_count, AVG(age) AS avg_age
FROM users
WHERE created_at >= '2026-01-01'    -- filters rows, before grouping
GROUP BY country
HAVING COUNT(*) > 100                -- filters groups, after aggregation
ORDER BY user_count DESC;

COUNT(*)          -- all rows, including NULLs
COUNT(column)     -- non-NULL values only
COUNT(DISTINCT x)
SUM(amount)
AVG(price)
MIN(date) / MAX(date)
STRING_AGG(name, ', ')   -- PostgreSQL
GROUP_CONCAT(name)       -- MySQL

COUNT(*) versus COUNT(column) is a standard interview question. COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. On a table with 100 rows where 30 have no email, COUNT(*) is 100 and COUNT(email) is 70.

Put filters in WHERE when you can. WHERE runs before grouping and reduces how many rows the expensive grouping step has to process. HAVING runs after. Filtering rows in HAVING gives the right answer more slowly.

Window Functions

The distinction that matters: GROUP BY collapses rows. A window function keeps them.

SELECT
  name,
  category,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category,
  AVG(price)  OVER (PARTITION BY category) AS category_avg,
  SUM(price)  OVER (ORDER BY created_at)   AS running_total
FROM products;

Every product row survives, with three computed columns added beside it. With GROUP BY you would have one row per category and no product names.

ROW_NUMBER()   -- 1,2,3,4 — always unique
RANK()         -- 1,2,2,4 — ties share, then skip
DENSE_RANK()   -- 1,2,2,3 — ties share, no skip
LAG(x, 1)      -- value from the previous row
LEAD(x, 1)     -- value from the next row

The classic use: "top 3 products per category." This is nearly impossible with GROUP BY alone and straightforward with a window function:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;

You need the subquery because window functions are computed at step 5, after WHERE has already run — so you cannot filter on rn in the same query level.

Subqueries and CTEs

-- CTE — readable, and can be chained
WITH high_value AS (
  SELECT customer_id, SUM(total) AS lifetime
  FROM orders
  GROUP BY customer_id
  HAVING SUM(total) > 1000
)
SELECT c.name, h.lifetime
FROM customers c
JOIN high_value h ON h.customer_id = c.id;

-- subquery in WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- EXISTS — usually faster than IN for large sets
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Prefer CTEs over nested subqueries for anything non-trivial. A three-level nested subquery and a three-step CTE do the same work; only one of them can be read by a colleague in six months.

EXISTS versus IN: EXISTS stops as soon as it finds one match, while IN may materialise the entire subquery result. On large subqueries EXISTS is usually faster. NOT IN also has a subtle trap — if the subquery returns any NULL, NOT IN returns no rows at all. Use NOT EXISTS.

Modifying Data

INSERT INTO users (name, email) VALUES ('Ada', 'ada@x.com');
INSERT INTO users (name, email) VALUES ('A', 'a@x.com'), ('B', 'b@x.com');

UPDATE users SET status = 'active' WHERE id = 5;
DELETE FROM users WHERE created_at < '2020-01-01';

-- upsert (PostgreSQL)
INSERT INTO users (email, name) VALUES ('a@x.com', 'Ada')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Always write the WHERE clause first. UPDATE users SET status = 'active' without a WHERE updates every row in the table, and there is no undo outside a transaction.

The habit that prevents it: write the query as a SELECT first, confirm it returns exactly the rows you intend, then change SELECT * to UPDATE ... SET.

BEGIN;
UPDATE users SET status = 'active' WHERE id = 5;
-- check the result
COMMIT;   -- or ROLLBACK;

Indexes and Performance

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_cust_date ON orders(customer_id, created_at);

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'a@x.com';

What to index: columns in WHERE, columns in JOIN ... ON, and columns in ORDER BY on large tables.

What EXPLAIN is telling you: look for a sequential scan or full table scan on a large table. That means the database is reading every row because no index helps it.

Four things that silently disable your index:

1. A function on the column — WHERE LOWER(email) = 'a@x.com' cannot use an index on email. Index the expression, or store the value already lowercased.
2. A leading wildcard — LIKE '%son' cannot use a standard index. LIKE 'John%' can.
3. Wrong column order in a composite index — an index on (customer_id, created_at) helps queries filtering on customer_id, or on both. It does not help a query filtering only on created_at.
4. Type mismatch — comparing an indexed integer column to a string may force a cast and skip the index.

Indexes are not free. Each one slows down every INSERT, UPDATE, and DELETE, because the index must be maintained. Index what you actually query, not every column.

The N+1 Problem

The most common performance bug in application code, and it is invisible in the SQL itself:

-- 1 query
SELECT * FROM posts LIMIT 100;
-- then, in a loop, 100 more queries
SELECT * FROM users WHERE id = ?;

101 round trips to the database instead of one. Each is fast; together they are catastrophic.

The fix is a single JOIN, or an IN clause with all the ids at once. Most ORMs call this eager loading, and it is usually one keyword you forgot to add.

The Five Traps

1. = NULL instead of IS NULL. Returns zero rows, silently, forever.

2. Filtering a LEFT JOIN in WHERE. Converts it to an INNER JOIN. Put the condition in ON.

3. UPDATE or DELETE without WHERE. Write it as a SELECT first. Every time.

4. NOT IN with a subquery containing NULL. Returns nothing at all. Use NOT EXISTS.

5. SELECT * in production code. It reads columns you discard, breaks when someone adds a column, and prevents index-only scans. Name your columns.

Print This Section

EXEC ORDER   FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

JOINS        INNER = matches only     LEFT = keep all left rows
             filter the right table in ON, never in WHERE

NULL         IS NULL / IS NOT NULL    never  = NULL
             NOT IN + any NULL = zero rows → use NOT EXISTS

FILTER       WHERE = rows (before grouping)
             HAVING = groups (after aggregation)

WINDOW       ROW_NUMBER() OVER (PARTITION BY x ORDER BY y)
             keeps all rows; GROUP BY collapses them

SAFETY       write UPDATE/DELETE as SELECT first

SQL rewards understanding execution order more than memorising syntax. Once you know that WHERE runs before SELECT and HAVING runs after GROUP BY, most confusing errors explain themselves.

🔗 Next in this collection: JavaScript Cheat Sheet You'll Use Every Day, or return to The Complete Developer Cheat Sheet Collection.