SQL Interview Questions (2026): From Joins to Window Functions

INNER JOIN returns only the rows that have a match in both tables. LEFT JOIN keeps every row from the left table and fills in NULLs for any right-table columns without a match. It's the most common opening filter question, and the follow-up almost always asks you to predict the row count before you run the query.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Interviewers want to hear that this query returns every employee, including anyone with a NULL or orphaned department_id, and that COUNT(d.name) on that same result set would come out lower than COUNT(*).

You get a fan-out. If a customer has 3 matching order rows and that same customer key matches 2 shipment rows, the join produces 6 rows for that customer, not 3 or 2. This is the single most common cause of a SUM() coming out two or three times too large in a report nobody double-checked.

Interviewers ask this to see if you'll actually reason about cardinality before running a query, instead of trusting whatever number comes back.

WHERE filters individual rows before grouping happens. HAVING filters groups after aggregation. You can't reference an aggregate function like AVG(salary) in a WHERE clause because at that point in execution, the grouping hasn't happened yet.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department_id
HAVING AVG(salary) > 90000;

COUNT(*) counts every row in the group, full stop. COUNT(column_name) counts only the rows where that specific column isn't NULL. If 40 out of 500 employees have no manager_id, COUNT(manager_id) returns 460 while COUNT(*) returns 500 on the same table.

A clustered index determines the physical order rows are stored in on disk, so a table can have only one. A non-clustered index is a separate structure that stores the indexed column plus a pointer back to the actual row. In MySQL's InnoDB, every table is clustered on its primary key whether you ask for it or not, and secondary indexes store the primary key value as that pointer rather than a raw disk address.

1NF requires atomic values, no repeating groups or comma-separated lists jammed into a single column. 2NF requires 1NF plus no partial dependency on a composite key, every non-key column must depend on the whole key, not just part of it. 3NF requires 2NF plus no transitive dependency, a non-key column can't depend on another non-key column.

A classic violation: a single orders table with customer_name and customer_email columns repeated on every order row. That's a transitive dependency (customer_email depends on customer_name, not on the order itself), and it's what customers and orders tables split apart to fix.

Atomicity means a transaction either fully commits or fully rolls back, no partial writes. Consistency means the database moves from one valid state to another, respecting constraints. Isolation means concurrent transactions don't see each other's uncommitted changes. Durability means once a transaction commits, it survives a crash.

DELETE is DML. It removes rows one at a time, can be filtered with a WHERE clause, fires any triggers defined on the table, and is fully transactional, meaning you can roll it back before you commit. Because it's logged row by row, deleting millions of rows with DELETE can be slow and bloats the transaction log.

TRUNCATE is DDL in most engines. It deallocates the table's data pages in one operation instead of logging each row, so it's dramatically faster on large tables, but you lose the ability to filter rows, it's all or nothing. It also resets any identity or auto-increment counter back to its seed value and typically doesn't fire row-level triggers. TRUNCATE needs a schema lock, so it fails if the table is referenced by an active foreign key you haven't disabled, or if anything else is holding a lock on it.

DROP removes the table definition entirely, its data, indexes, constraints, and permissions all go with it. There's no coming back from a DROP without a backup, whereas DELETE can be undone mid-transaction and TRUNCATE, while hard to undo, at least leaves the table structure intact.

A primary key uniquely identifies each row in its own table. It can't contain NULLs, a table can only have one, and most engines automatically build a unique index on it, which is why lookups by primary key are fast by default.

A foreign key lives on a different table and points at the primary key, or any unique key, of another table. Its job isn't uniqueness, it's referential integrity, it stops you from inserting a row that references a parent that doesn't exist, and it controls what happens when that parent row is deleted or updated through CASCADE, SET NULL, or RESTRICT.

The part people forget: a foreign key column can absolutely contain NULL unless you've also added a NOT NULL constraint on it. That's actually useful, an employee with no manager yet or an order with no assigned driver yet both use a nullable foreign key on purpose.

GROUP BY collapses the rows that share the same value in the grouping columns into a single output row per group, so you can run aggregate functions like SUM, COUNT, or AVG against each group instead of against the whole table.

Any column you SELECT that isn't wrapped in an aggregate has to appear in the GROUP BY clause because once rows are collapsed into one row per group, the database has no way to know which of the possibly many values for that column in that group you actually want. If you had ten orders in a group and picked customer_name without grouping on it, which of the ten names should it return? Standard SQL refuses to guess, though MySQL historically let this slide with ONLY_FULL_GROUP_BY turned off and would silently return an arbitrary row's value, which is a real source of bugs people don't notice until the data shifts.

Both stack the result sets of two or more SELECT statements on top of each other, and both require the same number of columns with compatible types in the same order. The difference is that UNION removes duplicate rows across the combined result, while UNION ALL keeps every row exactly as it came out of each query.

That dedup step means UNION has to sort or hash the entire combined output to find duplicates, which costs real time and memory on large result sets. If you already know the two queries can't produce overlapping rows, or you don't care about duplicates, UNION ALL is the better default because it skips that work entirely. A lot of slow reporting queries turn out to have an unnecessary UNION where UNION ALL would've done the same job faster.

NULL represents an unknown or missing value, it isn't the same thing as zero, an empty string, or false. Because it means unknown, any comparison against NULL using =, !=, <, or > doesn't evaluate to true or false, it evaluates to unknown, and a WHERE clause only keeps rows where the condition is true, so rows involving unknown get filtered out either way.

That's why "WHERE column = NULL" always returns zero rows, even for rows where the column genuinely is NULL. You have to use IS NULL or IS NOT NULL instead, special operators built specifically to test for the presence or absence of a value rather than to compare values. This also trips people up in NOT IN subqueries, if the subquery returns even one NULL, the whole NOT IN comparison can silently return no rows at all, which is a classic production bug.

CHAR is fixed length, if you declare CHAR(10) and store 'cat', the database pads the remaining 7 bytes with spaces and always uses the full 10 bytes of storage. VARCHAR is variable length, it stores only the characters you give it plus a small length prefix, usually 1 or 2 bytes, so 'cat' in a VARCHAR(10) column only takes up the space 'cat' needs.

CHAR can occasionally be marginally faster for fixed-width data like a two-letter country code or a fixed-format reference number, because the engine doesn't need to track variable offsets. But for almost everything else, names, addresses, free text, VARCHAR is the right default since it avoids wasting storage and avoids having to trim trailing spaces when you compare or display the value.

DISTINCT removes duplicate rows from a result set, based on every column you've selected together, not just one of them. If you SELECT DISTINCT city, state and two rows have the same city but different states, both rows stay, because DISTINCT is comparing the whole row, not just city.

The place it bites people is when they slap DISTINCT onto a query with a join that's producing duplicate rows, instead of fixing the join. That masks the real problem, the join is fanning out more rows than it should, and DISTINCT just hides the symptom while still doing the expensive work of sorting or hashing the full result set to dedupe it. It's a lot cheaper and more correct to fix the join condition than to rely on DISTINCT to clean up after it.

SQL reads top to bottom but doesn't execute that way. The real order is roughly FROM and JOIN first, to build the working set of rows, then WHERE to filter individual rows, then GROUP BY to collapse rows into groups, then HAVING to filter those groups, then SELECT to compute the actual output columns and aliases, then DISTINCT, then ORDER BY, and finally LIMIT or OFFSET.

That's exactly why you can't reference a column alias you defined in SELECT inside a WHERE clause, WHERE runs before SELECT has computed anything. But you generally can use that alias in ORDER BY, because ORDER BY runs after SELECT. It also explains why HAVING can reference aggregate functions and WHERE can't, aggregates don't exist yet at the point WHERE runs.

Join the employees table to itself, once as the employee and once as the manager, matching each row's manager_id to the manager's employee_id. Then filter on salary comparison.

SELECT e.name AS employee, m.name AS manager,
       e.salary AS employee_salary, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

This question probes whether you actually understand what a self-join represents (two logical roles playing out on one physical table), not whether you can memorize the pattern. Interviewers frequently follow up by asking what happens to employees with a NULL manager_id, the top of the org chart. INNER JOIN silently drops them, which is usually the correct behavior here but worth saying out loud.

Union a LEFT JOIN with a RIGHT JOIN of the same two tables. The LEFT JOIN covers every left-table row with or without a match; the RIGHT JOIN covers every right-table row with or without a match. UNION (not UNION ALL) removes the rows that show up in both, which are the actual matched pairs.

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.name, d.name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

Worth saying out loud in the room: PostgreSQL, SQL Server, and Oracle all support FULL OUTER JOIN natively, so this workaround only matters on MySQL. An interviewer who's tested you on Postgres before probably wants to hear that you know the difference, not just the trick.

This one layers a window function inside a CTE, then aggregates the result. Compute each employee's department average with AVG() OVER (PARTITION BY department_id), keep only the rows where an individual's salary beats that average, then group and count.

WITH dept_avg AS (
  SELECT employee_id, department_id, salary,
         AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
  FROM employees
)
SELECT department_id, COUNT(*) AS above_avg_count
FROM dept_avg
WHERE salary > avg_salary
GROUP BY department_id
HAVING COUNT(*) > 5;

Interviewers use this to check whether you understand that a window function runs before the outer GROUP BY sees the rows, which is exactly why you need the CTE instead of trying to nest AVG() OVER inside HAVING directly (most engines will reject that).

A non-correlated subquery runs once, independently, and its result gets reused for every row of the outer query. A correlated subquery references a column from the outer query, so conceptually it re-evaluates once per outer row. That's also why correlated subqueries are the more common performance complaint in code review.

/* non-correlated: runs once */
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

/* correlated: references e from the outer query */
SELECT name FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees e2
  WHERE e2.department_id = e.department_id
);

EXISTS just checks whether a matching row is present and stops looking the moment it finds one. IN materializes the full subquery result and compares against it. The real gotcha shows up with NOT IN: if the subquery's result set contains even a single NULL, NOT IN silently returns zero rows for every outer row, because comparing against NULL evaluates to unknown rather than true or false. NOT EXISTS doesn't have that problem.

SELECT d.name FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

This is a genuinely useful thing to know outside of interviews. It's caused real production bugs where a report quietly went to zero rows for weeks before anyone noticed.

All three number rows within a window, but they handle ties differently. Say three employees tie for the highest salary in a department. ROW_NUMBER gives them 1, 2, 3 arbitrarily, ignoring the tie entirely. RANK gives them all a 1, then skips to 4 for the next distinct value. DENSE_RANK also gives them all a 1, but the next distinct value gets 2, with no gap.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

Almost every interviewer follows up by asking which one you'd use for a top-N-per-group problem, and whether ties should count as one slot or several. There's no universally correct answer, it depends on what the business question actually is, and saying so is a better answer than picking one confidently and moving on.

DENSE_RANK partitioned by department gives you exactly this: ties at the third-highest salary all get included, since they all share rank 3.

SELECT * FROM (
  SELECT employee_id, name, department_id, salary,
         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

LAG() reaches back to the previous row within a partition, without a self-join. Subtract it from the current row's value.

SELECT sale_date, store_id, revenue,
       revenue - LAG(revenue) OVER (
         PARTITION BY store_id ORDER BY sale_date
       ) AS day_over_day_change
FROM daily_sales;

LEAD() works the same way but looks forward instead of back. Both took over a job that used to require a self-join on date minus one, and that older approach is still worth knowing if your interviewer asks you to solve it without window functions.

SUM() as a window function with an explicit frame gives you a cumulative total that resets per customer.

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (
         PARTITION BY customer_id ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders;

The frame clause (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is actually the default behavior when you use ORDER BY inside OVER() without specifying a frame, but writing it out explicitly is the better habit and tends to read as more deliberate in an interview.

Every index has to be maintained on every write. Add an index and each INSERT, UPDATE, or DELETE now updates that index's structure too, on top of the table itself. If the column you indexed never actually shows up in a WHERE, JOIN, or ORDER BY clause that the query planner can use, you've paid the full write cost for zero read benefit. It's a straightforward trade, but a surprising number of candidates only think about indexes as a pure win.

A B-tree composite index is sorted by the leftmost column first, then the second column within each value of the first. Searching by first_name alone is like trying to find a name in a phone book sorted by last name. You'd have to scan the whole thing. The index works fine for queries filtering on last_name alone, or on last_name plus first_name together, just not first_name in isolation.

Reporting and analytics tables are the obvious case. A dashboard querying a fully normalized schema might need six joins to answer one question; a pre-aggregated or flattened reporting table answers it with none. The trade is storage and update complexity for read speed, and it's usually the right trade for a table that gets read thousands of times for every one time it's written.

READ COMMITTED only guarantees you never read uncommitted (dirty) data, but if you run the same SELECT twice within one transaction, you might get different results because another transaction committed a change in between. REPEATABLE READ guarantees the same query returns the same rows for the life of the transaction.

Worth knowing for the room: MySQL's InnoDB engine actually goes further than the SQL standard requires at REPEATABLE READ. It uses next-key locking to also block phantom reads (new rows appearing that match your WHERE clause), which the ANSI standard doesn't technically promise at that isolation level. PostgreSQL's REPEATABLE READ, by contrast, follows the stricter snapshot-isolation model. Same isolation level name, different guarantee, and that's exactly the kind of detail a senior-level interviewer is listening for.

Two transactions each hold a lock the other one needs, in reverse order. Transaction A locks row 1 and waits for row 2; transaction B has already locked row 2 and is waiting for row 1. Neither can proceed. Most databases detect this through a wait-for graph and pick one transaction as the deadlock victim, rolling it back automatically so the other can finish.

The actual fix isn't in the moment, it's upstream: make sure every code path that touches multiple rows locks them in the same order, keep transactions short, and index the columns involved so locks are scoped to fewer rows in the first place.

The portable answer, works on every dialect: take the max salary that's still less than the overall max.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

This returns the second-highest distinct salary value, not the second row in a sorted list, and that distinction matters if two people tie for first. Most interviewers haven't thought through what their own question means when there's a tie. Asking them to clarify is a fair move, not a stall tactic.

First identify the duplicates by grouping on whatever columns define a duplicate for your case, then filter to groups with more than one row.

SELECT name, department_id, COUNT(*) AS occurrences
FROM employees
GROUP BY name, department_id
HAVING COUNT(*) > 1;

To actually delete the extras and keep the lowest employee_id in each duplicate set, MySQL supports a multi-table DELETE syntax:

DELETE e1 FROM employees e1
JOIN employees e2
  ON e1.name = e2.name
  AND e1.department_id = e2.department_id
WHERE e1.employee_id > e2.employee_id;

PostgreSQL doesn't support that exact syntax; it uses a USING clause instead of a second JOIN target, same logic:

DELETE FROM employees e1
USING employees e2
WHERE e1.name = e2.name
  AND e1.department_id = e2.department_id
  AND e1.employee_id > e2.employee_id;

DENSE_RANK handles ties the way most business questions actually mean them (the 2nd-highest tier of pay, not the 2nd row).

SELECT salary FROM (
  SELECT DISTINCT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 5; /* Nth = 5, swap for whatever N is given */

If ties genuinely don't matter for the question being asked, LIMIT/OFFSET or TOP is a shorter path: LIMIT 1 OFFSET 4 for the 5th-highest on MySQL or Postgres, or FETCH FIRST 1 ROW ONLY after an OFFSET on Oracle 12c+. Just be ready to explain why you picked one approach over the other.

A window frame of 6 rows back plus the current row gives a 7-day rolling window, assuming one row per calendar day with no gaps.

SELECT signup_date, daily_count,
       AVG(daily_count) OVER (
         ORDER BY signup_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS trailing_7day_avg
FROM signups;

Worth flagging to the interviewer: ROWS BETWEEN counts physical rows, not calendar days. If a store is closed on Sundays and has no row for that date, this window silently shifts to cover 7 rows spanning more than 7 actual days. RANGE BETWEEN with an interval, where supported, fixes that, but it's less portable across dialects.

A scalar subquery returns exactly one column and one row, and you use it anywhere a single value would go, in a SELECT list, in a WHERE comparison, in an assignment. If it accidentally returns more than one row, most engines throw a runtime error rather than silently picking one.

A derived table is a subquery used in the FROM clause, wrapped in parentheses and given an alias, and it behaves like a temporary, unnamed view for the duration of that one query. You join against it, filter it, aggregate it, just like a real table, and the optimizer decides whether to materialize it or fold it into the outer query plan. The practical difference in day-to-day writing is a scalar subquery gives you one number, a derived table gives you a whole result set you can keep working with.

A CTE is scoped to a single statement, it doesn't persist, isn't indexed on its own, and in most engines is either inlined into the main query or, in some cases, materialized once and reused within that statement. It's the right tool when you want to name an intermediate step for readability, break a complicated query into logical stages, or write a recursive query, since recursive CTEs are the standard way to walk a hierarchy.

A temp table is the better choice when you need to reuse the same intermediate result across multiple statements in a session or script, or when the intermediate result is large enough that you want to add an index to it before joining against it again, something a CTE can't do. A view is for something you want named and reusable across many different queries and sessions going forward, it's persisted as metadata in the schema, not just for the life of one query.

An INNER JOIN matches rows between two tables based on a join condition, only rows that satisfy that condition make it into the result. A CROSS JOIN has no join condition at all, it produces the full Cartesian product, every row in the first table paired with every row in the second, so joining a 100-row table to a 200-row table with CROSS JOIN gives you 20,000 rows.

Where it's genuinely useful is generating combinations you don't already have, like producing a row for every store and product pair to fill in zero-sales days a sales table wouldn't otherwise report, or cross joining a calendar table against a list of customers to build a complete date scaffold before a LEFT JOIN back to actual activity. Outside of deliberate combination generation, a CROSS JOIN in a query almost always means someone forgot the ON clause, and it's worth double-checking row counts when you see one you didn't write on purpose.

The portable way is conditional aggregation: group by whatever you want as your row key, and for each column you want to produce, use a CASE expression inside an aggregate function that only counts or sums the rows matching that category.

SELECT
  product_id,
  SUM(CASE WHEN month = 'Jan' THEN sales_amount END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN sales_amount END) AS feb,
  SUM(CASE WHEN month = 'Mar' THEN sales_amount END) AS mar
FROM monthly_sales
GROUP BY product_id;

This works in every mainstream database without depending on a vendor-specific PIVOT syntax, and it's easy to extend, add another CASE for another column. The tradeoff is you have to know your categories ahead of time to write the CASE expressions, it isn't dynamic. If the set of columns is unknown until runtime, you generally have to build the SQL string dynamically in application code or a stored procedure, since standard SQL can't produce a variable number of output columns from a single static query.

Functionally they enforce the same thing, no two rows can share the same value in that column or set of columns. The distinction is mostly about intent and metadata: a UNIQUE constraint is a schema-level rule documented as part of the table's business logic, while a UNIQUE index is the physical structure, usually a B-tree, that actually enforces it. In practice, creating a UNIQUE constraint in most databases automatically creates a backing unique index to do the enforcement, so the line between them is thin.

The NULL behavior is the part people get wrong. In PostgreSQL, SQL Server, MySQL, and SQLite, a unique constraint treats each NULL as distinct from every other NULL, so you can insert multiple rows with NULL in that column even though the column is unique. This matters for something like an optional external_reference_id column you want unique only when it's actually populated, most engines let you do that for free, but it's worth verifying on whichever engine you're on rather than assuming.

OFFSET/LIMIT pagination asks the database to skip a number of rows and then return the next batch. The problem is the database still has to scan and sort past all the skipped rows to know what to skip, it can't jump straight to row 100,000, so page 1000 of a big table can be dramatically slower than page 1, even though both pages return the same number of rows. It also has a correctness problem: if rows are inserted or deleted between page loads, users can see the same row twice or skip one entirely, because OFFSET is defined purely by position, not by content.

Keyset, or seek, pagination instead remembers the last row's sort key from the previous page and asks for rows strictly after it, something like "WHERE (created_at, id) > (last_created_at, last_id) ORDER BY created_at, id LIMIT 20." That lets the index do a direct seek to the right starting point regardless of how deep into the table you are, so performance stays flat as you page further, and it's stable against concurrent inserts and deletes. The tradeoff is you lose the ability to jump to an arbitrary page number, keyset pagination is naturally next-page oriented, not give-me-page-47 oriented.

A covering index is an index that contains every column a query needs, both the columns in the WHERE clause and the columns in the SELECT list, so the engine can answer the query entirely from the index without ever touching the underlying table's data pages. That extra step of going back to the table to fetch remaining columns is usually called a key lookup or bookmark lookup, and it's one of the more expensive parts of an otherwise indexed query when it happens per row.

You check it by looking at the actual execution plan, EXPLAIN in Postgres and MySQL, or the graphical or text plan in SQL Server, and looking for an index-only scan in Postgres, "Using index" in MySQL's Extra column, or the absence of a Key Lookup or RID Lookup operator in SQL Server. If you see the engine doing an index seek followed by a separate lookup back to the table for every matched row, the index isn't covering, and adding the missing SELECT columns to the index, often as INCLUDE columns rather than key columns, can turn it into one.

The anchor member picks out employees with no manager (the top of the chart). The recursive member joins the CTE back to employees on manager_id, incrementing depth each pass, until no more rows match.

WITH RECURSIVE org_chart AS (
  SELECT employee_id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY depth;

MySQL 8.0+, PostgreSQL, and SQL Server all support this syntax with the WITH RECURSIVE keyword (SQL Server just uses WITH). Older MySQL versions have no recursive CTE support at all, which is worth mentioning if the interviewer asks about legacy systems. Use UNION ALL here, not UNION, since an org chart has no duplicate rows to remove and UNION's dedup step adds cost for nothing.

This is the pre-window-function fallback, and it's still asked specifically to test whether window functions are the only tool in your kit or whether you actually understand the underlying logic.

SELECT e.*
FROM employees e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

Notice this correlated-subquery version returns every employee tied for the department max, the same tie behavior as the DENSE_RANK version earlier in this page, just written the older way. If an interviewer wants exactly one row per department even with ties, that's a meaningfully different (and arguably worse-specified) question, and it's fair to ask which one they mean.

The optimizer estimates the cost of each possible plan and picks the cheapest one it can find within its search budget, it isn't checking every possible plan exhaustively for anything beyond a handful of tables. The core input to that cost estimate is cardinality, how many rows a given operation is expected to return, and that estimate comes from statistics the database keeps on column value distributions, histograms, distinct value counts, and null fractions, refreshed periodically or on demand.

If a predicate is expected to match a small fraction of the table, say under 1 to 5 percent depending on the engine and the table's size, an index seek plus a lookup back to the table is usually cheaper than reading every page. But if the predicate is expected to match a large fraction, a full table or full index scan can actually be cheaper, because a scan reads pages sequentially while a seek-plus-lookup pattern does random I/O per matching row, and random I/O is expensive on spinning disks and still meaningfully slower than sequential reads even on SSDs.

This is exactly why stale statistics cause bad plans: if the optimizer thinks a predicate matches 50 rows when it actually matches 5 million, because the stats haven't been updated since a big data load, it'll pick a seek-and-lookup plan that ends up doing millions of random lookups instead of one sequential scan, and the query that used to run in milliseconds suddenly takes minutes. Updating statistics, or in extreme cases forcing a specific plan with a hint, is the direct fix.

Under READ COMMITTED, the first transaction to issue an UPDATE on that row acquires a row-level exclusive lock and proceeds. The second transaction's UPDATE on the same row blocks, it physically waits, until the first transaction commits or rolls back. Once the first commits, the second transaction's UPDATE re-reads the now-committed value and applies its own change on top of it. This is why READ COMMITTED alone doesn't protect against lost updates when the logic is read a value, compute a new value in application code, then write it back: transaction two might read the row before transaction one's UPDATE, compute off the stale value, then get blocked, then apply an update that overwrites transaction one's change based on data that's no longer current.

Under SERIALIZABLE, the database has to guarantee the result is equivalent to the two transactions running one after another in some order, not just protect individual row writes. Depending on the engine, this is done either with stricter locking that also locks the rows a transaction merely read as part of deciding what to write, or with optimistic concurrency control that tracks read and write dependencies and aborts one transaction with a serialization failure if it detects the two transactions' operations couldn't have been safely interleaved. In Postgres specifically, you'd literally get a "could not serialize access due to concurrent update" error on one of the two transactions, and the application is expected to catch that and retry.

The tradeoff is real: SERIALIZABLE gives you the strongest guarantee but at the cost of aborted transactions under contention that your application has to retry, while READ COMMITTED never aborts for this reason but leaves you responsible for using explicit row locking, SELECT ... FOR UPDATE, or an atomic single-statement UPDATE like "UPDATE accounts SET balance = balance - 10 WHERE id = 5" whenever the update depends on the current value, rather than reading, computing, then writing in three separate steps.

A non-repeatable read is when you read the same row twice within one transaction and get different values, because another transaction updated and committed a change to that specific row in between your two reads. A phantom read is one level up: you run the same query twice within one transaction and get a different set of rows entirely, because another transaction inserted or deleted rows matching your query's condition in between, not because any row you'd already seen changed value.

Concretely: transaction A runs "SELECT COUNT(*) FROM orders WHERE status = 'pending'" and gets 40. Before transaction A runs that same query again, transaction B inserts a new order with status 'pending' and commits. If transaction A's isolation level is only REPEATABLE READ, protecting against non-repeatable reads on rows it's already touched isn't the same as protecting against new rows appearing that match its WHERE clause, so transaction A's second run of that query can return 41, a phantom. Only SERIALIZABLE, or in Postgres's implementation its stricter snapshot-based enforcement, actually guarantees the second run returns the same 40.

This is a real production concern for anything doing check-a-count-then-act-on-it logic inside one transaction, like an inventory system checking how many seats are left before confirming a booking. If you're not on SERIALIZABLE or using an explicit locking read, a phantom insert from a concurrent transaction can let you oversell.

The core idea is to delete in small batches inside their own short transactions instead of one giant DELETE, so each individual transaction holds its locks for milliseconds instead of minutes and lets other traffic interleave between batches.

DELETE FROM event_log
WHERE id IN (
  SELECT id FROM event_log
  WHERE created_at < '2024-01-01'
  ORDER BY id
  LIMIT 5000
);

Run that in a loop, checking rowcount, stopping when it hits zero, with a short pause between iterations if replication lag or lock contention is a concern. A few things matter for doing this safely in production: pick a batch size small enough that each delete finishes fast, a few thousand rows is a common starting point, tuned by watching lock wait times, order by an indexed column like the primary key rather than scanning without one, and if the table has foreign keys or triggers, make sure those aren't themselves doing expensive per-row work that defeats the point of batching.

On top of that, watch out for the transaction log or WAL growing unexpectedly, since each batch still generates full logging, and watch replica lag if you're on a replicated setup, since a few thousand small deletes can still add up to a lot of write volume the replicas have to keep up with. If this is a recurring cleanup rather than a one-time job, partitioning the table by date and dropping whole old partitions is a far cheaper long-term answer than looping DELETE statements, since dropping a partition is close to instant regardless of row count.

A nested loop join takes each row from the outer, usually smaller or smaller-after-filtering, input and, for every one of those rows, probes the inner input for matches, ideally using an index so that probe is fast rather than a full scan. It's the right choice when one side is small or the join predicate is highly selective against an indexed inner table, because the cost scales with outer rows times cost per inner probe, and that stays cheap when either factor is small.

A hash join builds an in-memory hash table on the smaller input keyed by the join column, then streams the larger input through it, probing the hash table for matches. It doesn't need either input to be sorted or indexed, which makes it a strong default for joining two large, unsorted tables where nested loop's per-row probing would be too slow. Its weakness shows up when the build side doesn't fit in memory, forcing the engine to spill to disk in batches, which is noticeably slower and shows up in a plan as a hash join with spill activity.

A merge join requires both inputs to already be sorted, or sorts them first, on the join key, then walks both sorted streams in lockstep, advancing whichever side is behind, similar to the merge step in merge sort. It's efficient when both inputs are already sorted, for example because they're coming off an index scan on the join column, since then there's no separate sort step needed at all. The optimizer weighs the cost of any required sort or hash build against the expected row counts and available memory, which is why the same query can get a different join type after a data volume changes significantly or after statistics are refreshed.

The standard trick is to subtract a row number from the actual date. Within a consecutive run of dates, the date increases by exactly one each row and so does the row number, so the difference between them stays constant for the whole run and only changes when a day is skipped. That constant difference becomes your grouping key for each island.

WITH numbered AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ))::int AS grp
  FROM daily_logins
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM numbered
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

The date-minus-row-number arithmetic needs adjusting per engine, Postgres lets you subtract an integer number of days from a date directly, SQL Server needs DATEADD, MySQL needs DATE_SUB or INTERVAL arithmetic, but the logic is identical everywhere. Once you've got streak_start, streak_end, and streak_length per group, finding someone's longest ever streak is just an ORDER BY streak_length DESC LIMIT 1 per user, and finding the current active streak is filtering to the group whose streak_end is today or yesterday.

With unique constraints, most engines, Postgres, SQL Server, MySQL, and SQLite, treat every NULL as distinct from every other NULL, so a unique column can hold multiple NULL rows even though it's unique. That's usually convenient, but it means you can't rely on a unique constraint alone to guarantee at most one row where this optional field is unset, you'd need a partial or filtered unique index for that.

With foreign keys, a NULL in the foreign key column simply means no relationship, it isn't checked against the parent table at all, the row passes validation regardless of what's in the parent table. The gotcha shows up with composite, multi-column, foreign keys: by default, MATCH SIMPLE, which is what most engines implement, if any one column in the composite key is NULL, the whole foreign key check is skipped, even if the other columns are populated with values that don't exist in the parent. People expect partial matching to still be enforced and it isn't, unless you explicitly use MATCH FULL, which most engines outside Postgres don't even support.

With aggregates, SUM, AVG, MAX, MIN, and COUNT(column) all silently ignore NULL values rather than treating them as zero, so AVG of 10, NULL, 20 is 15, not 10, because the NULL row is excluded from both the sum and the count rather than counted as a zero. COUNT(*) is the outlier, it counts rows regardless of NULLs in any column. This is exactly why COUNT(column_name) and COUNT(*) can diverge, and why an AVG that looks suspiciously high on a column with a lot of missing data is worth checking, because those missing rows are being dropped from the denominator entirely rather than dragging the average down.

Start with EXPLAIN (or EXPLAIN ANALYZE on Postgres, which actually runs the query and shows real timings rather than estimates). Check whether the planner switched from an index seek to a full table scan, which often happens once a table crosses a row-count threshold where the optimizer decides scanning is now cheaper than jumping through the index.

Next, look for a non-sargable predicate, something like WHERE YEAR(order_date) = 2026, which wraps the indexed column in a function and defeats the index entirely even though the column itself is indexed. Also check whether table statistics are stale; a query planner making decisions off month-old row-count estimates will pick badly, and running ANALYZE (or UPDATE STATISTICS on SQL Server) is often the whole fix. Only after ruling those out would I look at adding a genuinely new index.

First I'd isolate which join is responsible rather than guessing, by running the base table alone to get its true row count, then adding one join at a time and checking the row count after each addition. The join where the count jumps disproportionately, going from, say, 10,000 rows to 400,000 after adding one particular join, is the culprit.

The usual cause is joining against a table where the join key isn't actually unique on that side, a customers-to-orders join is fine because each order has one customer, but an orders-to-line-items join multiplies every order row by however many line items it has, and if you then also join line items to a shipments table where a single line item can have multiple partial shipments, you're multiplying twice. Once you've found the offending join, the fix is usually to aggregate that side down to one row per join key before joining, using a subquery or CTE that does the SUM, COUNT, or MAX first, rather than joining the raw detail table and aggregating afterward, which is both slower and, if you're not careful with COUNT(DISTINCT ...), can still produce wrong totals because of double-counting from the other joins.

A quick sanity check that's saved me more than once: compare COUNT(*) against COUNT(DISTINCT primary_key_of_the_grain_you_actually_want). If those numbers diverge, you have fan-out, and the ratio tells you roughly which join is multiplying, even before you go table by table.

A Type 2 SCD keeps history instead of overwriting it: every time an attribute changes, you insert a new row rather than updating the old one, and you track validity with something like effective_start_date, effective_end_date, NULL for the current row, and usually an is_current flag as a convenience column. The natural key, like customer_id, repeats across multiple rows, but a surrogate key uniquely identifies each version.

SELECT d.*
FROM dim_customer d
WHERE d.customer_id = 501
  AND '2026-03-15' >= d.effective_start_date
  AND ('2026-03-15' < d.effective_end_date OR d.effective_end_date IS NULL);

The is_current flag is a convenience for the common case of give-me-the-latest-version, it's not a substitute for the date range check when you actually need history as of a specific point in time. The gotcha most teams hit is off-by-one boundary handling on the effective dates, deciding consistently whether effective_end_date is inclusive or exclusive matters a lot once you're joining a fact table's transaction_date against this dimension, and mixing conventions between different SCD tables in the same warehouse is a recurring source of subtly wrong historical reports.