SQL Interview Questions

"An inner join keeps only the pairs that match on the join condition, so a customer with no orders just disappears. A left join keeps every row from the left table, and where there's no match on the right, the right-hand columns come back as NULL. A right join is the mirror image, and in practice I rewrite it as a left join by swapping the tables because it reads more easily. A full outer join keeps everything from both sides and fills NULLs wherever one side is missing, which is handy when I'm comparing two lists to see what's only in one. A cross join has no condition at all: every row meets every row, so three rows and four rows give twelve. One thing I always keep in mind is that if a key repeats on the other side, a join returns one row per match, so the row count can grow."

"I'd join the employees table to itself. I give it two aliases, e for the employee and m for the manager, and join on e.manager_id equals m.id. The important choice is a left join rather than an inner join, because the person at the top has a NULL manager_id and an inner join would silently drop them. Then I select e.name as employee and m.name as manager so the output is clear, and I might wrap the manager name in COALESCE to show 'No manager' instead of NULL. The same pattern works for any table that points at itself, like categories with a parent category, or comments that reply to other comments."

"Say I left join customers to orders and add WHERE o.status = 'shipped'. For customers with no orders, every order column is NULL, and NULL equals 'shipped' isn't true, so WHERE throws those customers away. The query now behaves exactly like an inner join. The fix is to put that condition in the ON clause instead. In ON, it only decides which orders count as a match. Every customer still comes through, and the ones without a shipped order just get NULLs. The rule I keep in my head is that ON controls matching and WHERE filters the final result. For an inner join the two places give the same answer, but for an outer join they don't."

"My default is NOT EXISTS: select customers where there isn't an order with that customer_id. It's clear and it handles NULLs correctly. Another way is a left join from customers to orders, keeping rows where the order's id is NULL. The one I'm careful with is NOT IN. If I write id NOT IN a subquery of order customer ids, and any order has a NULL customer_id, the result is empty. That's because NOT IN is a chain of not-equal checks, and a comparison with NULL is unknown, so no row ever passes. If I do use NOT IN, I add customer_id IS NOT NULL inside the subquery. Many optimizers turn NOT EXISTS and the left join version into a similar anti-join, but I'd check the plan rather than assume."

"Totals that are too high usually mean a join is repeating rows. For example, if the query joins orders to both order_items and payments, an order with three items and two payments turns into six rows, and a SUM of the order's total counts it six times. DISTINCT might make a list of rows look right, but it doesn't fix the SUM, and SUM(DISTINCT) is worse, because two different orders with the same total would be counted once. So I'd check the row count per order before and after each join to find where it multiplies. The proper fix is to aggregate each child table down to one row per order in its own subquery or CTE, then join those results. I'd walk the teammate through the six-row example, because it's a mistake almost everyone makes once."

"WHERE runs on individual rows before any grouping happens, so it can't see aggregates like COUNT or SUM. HAVING runs after GROUP BY and filters the groups themselves. A simple example: I want students who sat more than three exams since the start of 2025. The date condition goes in WHERE, because it's about single exam rows, and it also means fewer rows get grouped. Then I group by student and put COUNT(*) > 3 in HAVING, because that's a fact about the group, not about any one row. You can technically put a plain condition on a grouped column in HAVING, but I keep row conditions in WHERE because it's clearer and filters earlier."

"The clauses are written in one order but processed logically in another. First FROM and the joins build the rows, then WHERE filters them, then GROUP BY forms groups and HAVING filters those groups. Only after that does SELECT work out the output columns, including window functions, then DISTINCT removes repeats, ORDER BY sorts, and LIMIT or TOP cuts the result. That explains a few classic errors. I can't use a SELECT alias in WHERE, because WHERE runs before SELECT, but I can use it in ORDER BY. I can't put COUNT in WHERE, because the groups don't exist yet. And a window function can't go in WHERE either, so I wrap it in a CTE and filter outside. This is the logical order, though. The optimizer is free to run things differently as long as the answer comes out the same."

"Both stack the results of two queries on top of each other. UNION then removes duplicate rows across the whole result, which means the database has to sort or hash everything to find them. UNION ALL skips that and just returns every row. My default is UNION ALL, because often the two sets can't overlap anyway, like a live table and an archive table, and then UNION is paying for work that does nothing. If duplicates might exist and I really need them gone, I use UNION on purpose. Either way, both queries need the same number of columns in the same order with compatible types, and the column names come from the first query. That's different from a join, which puts columns side by side instead of stacking rows."

"In my last role I was testing an order flow, and a few customers were getting two confirmation emails. The app logs weren't clear, so I went to the data. First I grouped the orders table by customer and cart id with HAVING COUNT(*) > 1, and found a small number of carts with two orders created within a second of each other. Then I joined to the payments table and saw both orders carried the same payment reference. That pointed to a double click on the pay button sending two requests. I shared the query and the rows with the developer. They added a unique constraint on the cart id and disabled the button after the first click, and I turned my duplicate query into a check that runs after every test cycle, so it would flag the problem if it ever came back."

"A normal subquery runs once and hands back a result. A correlated one refers to the current outer row, so logically it's re-run for each row. Here, for each result row I compute the average score of the rows with the same class_id, and keep the row if its score is higher. It reads naturally, and many optimizers rewrite it into a join, but on a big table it can be slow if the engine really does run it row by row. The alternative I'd often use is a window function: AVG(score) OVER (PARTITION BY class_id) in a CTE, then filter on it in the outer query. That reads the table once, and I can show the average next to each score, which people usually want anyway."

"I use a CTE when a query has steps. Instead of nesting a subquery inside a subquery, I write WITH filtered as, then ranked as, then a final select, and anyone reading it can follow the logic top to bottom. It also helps when I need the same derived result twice in one query, and it's the only way to write a recursive query. On speed, I don't assume a CTE is faster. In many engines the optimizer treats a simple CTE just like an inline subquery and produces the same plan. Some engines, or older versions of them, compute the CTE once and store the result, which can help when it's reused but can hurt because filters from outside may not get pushed into it. So I write it for clarity first and let the query plan settle the speed question."

"This is a recursive CTE. The anchor part selects the direct reports of the manager I start from, say id 7, and marks them depth 1. Then UNION ALL with the recursive part, which joins employees to the CTE itself where manager_id equals the id of someone already found, and adds one to depth. The database keeps running that step until it finds no new rows. The risk is bad data: if two people end up as each other's managers, it loops forever. So I add a depth limit in the recursive part, or track the path and stop when an id repeats. The keyword differs a little by engine: PostgreSQL and MySQL want WITH RECURSIVE, while SQL Server just uses WITH."

"All three need an ORDER BY inside OVER, and optionally a PARTITION BY to restart the numbering per group. Say the scores are 95, 90, 90 and 85. ROW_NUMBER gives 1, 2, 3, 4. Every row gets a unique number, and which of the two 90s gets 2 isn't guaranteed unless I add a tiebreaker column to the ORDER BY. RANK gives 1, 2, 2, 4, like a sports table: two people tied for second, so nobody is third. DENSE_RANK gives 1, 2, 2, 3, with no gaps. I pick based on the question. For exactly one row per group, ROW_NUMBER. For the second highest distinct value, DENSE_RANK. For a leaderboard where a tie pushes the next person down, RANK."

"First I'd ask what second highest means. If the scores are 100, 100 and 90, most people mean 90, the second distinct value. The simplest query is the max of all scores below the overall max. That handles the tie at the top, and if every score is the same, MAX over no rows returns NULL, which is a clean way of saying there isn't one. The more general version uses DENSE_RANK ordered by score descending and keeps rank 2, which extends easily to the Nth highest. With that version, if there's no rank 2 you get no rows, so if the caller needs a single NULL I wrap it in a scalar subquery. I'd avoid LIMIT 1 OFFSET 1 on its own, because with a tie at the top it returns 100 again."

"I rank inside each class with a window function partitioned by class_id and ordered by score descending, inside a CTE, and then filter the rank in the outer query. I can't filter it in the same level because WHERE is worked out before window functions. The real decision is ties. If the rule is exactly three rows per class, I use ROW_NUMBER and add a tiebreaker, like the earlier submission time, so the result is repeatable. If two students tied for third should both appear, I use RANK and keep rank three or less, which can return four rows. DENSE_RANK would keep the top three distinct scores, which could mean many more people. I'd confirm which rule the person asking wants, because each one is a different answer."

"LAG looks back a set number of rows inside the window, one by default. I partition by sensor_id so each sensor is only compared with itself, and order by read_at so previous means earlier in time. Then the change is just the reading minus the lagged reading. For each sensor's first reading there's nothing before it, so LAG returns NULL and the change is NULL too, which I think is honest. If I wanted zero instead, LAG takes a third argument as a default, but that can hide the fact that there was no earlier reading. The mirror function, LEAD, looks forward, which is handy for working out how long each state lasted until the next one started."

"The running total is SUM of points over a window ordered by date, partitioned by account so it restarts for each one. The subtle part is the frame. If I write ORDER BY without a frame, the default is RANGE between unbounded preceding and current row. RANGE works on values, not rows, so if two rows have the same date they're peers, and both show the total including both of them. That surprises people when the running total jumps two rows at once. If I want a true row-by-row total, I write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and add a tiebreaker like id to the ORDER BY, so the order is repeatable. Frames are also how I do moving averages, for example the current row and the six before it."

"I group by email and keep the groups where COUNT(*) is greater than one, sorted by the count so the worst cases are on top. Then I'd ask whether case and spaces matter, because in real data the duplicates are often 'Ann@mail.com' and 'ann@mail.com' with a trailing space. If those should count as the same, I group by LOWER(TRIM(email)) instead. To see the actual rows rather than just the emails, I'd join back to users, or use COUNT(*) OVER (PARTITION BY email) and keep rows where it's above one. Once it's cleaned up, the lasting fix is a unique constraint, or a unique index on the lower-cased value where the engine supports that, so it can't happen again."

"First I make the rule repeatable. I number the rows within each email with ROW_NUMBER, ordered by created_at and then id, so even two rows created at the same moment have a clear winner. Row number 1 is the keeper, and I delete every id whose number is greater than 1. I leave out rows with no email, or all of them would count as one group. Before running the delete, I run the same query as a SELECT and check the count matches the duplicate report. I run it inside a transaction so I can roll back, and on a real system I copy the rows I'm deleting into a backup table first. I'd also check child rows, like orders that point at the ids I'm removing, and move them to the keeper before the delete."

"NULL means unknown, so NULL equals NULL isn't true, it's unknown, and WHERE only keeps rows where the condition is true. That's why = NULL finds nothing and I have to write IS NULL. The same logic hits not-equal: WHERE status <> 'closed' also drops rows where status is NULL, which is a common hidden bug. In aggregates, COUNT(*) counts every row, but COUNT(middle_name) only counts rows where it isn't NULL, and AVG ignores NULLs, so the average of 10, NULL and 20 is 15, not 10. If NULL should mean zero, I say so with COALESCE. Sorting also differs by engine: some put NULLs first and some last, so if it matters I write NULLS FIRST or NULLS LAST where that's supported."

"DELETE works row by row. I can add a WHERE clause, it fires any delete triggers, each row change is logged, and inside a transaction I can roll it back. It's the slowest of the three on a big table. TRUNCATE empties the whole table in one go by releasing its storage, so it's much faster, but there's no WHERE, delete triggers don't fire, and it's often blocked if other tables point at it through foreign keys. Whether I can roll it back depends on the database: some treat it as a normal transactional statement, others commit it straight away. DROP removes the table itself, structure, indexes, permissions and all. So on a shared database I treat TRUNCATE and DROP as permanent unless I've checked the engine and I'm deliberately inside a transaction."

"A primary key identifies each row: it's unique and can't be NULL, and a table has only one, though it can span several columns. A unique constraint also blocks repeats, like two accounts with the same email, but a table can have many of them, and how many NULLs it allows depends on the engine. A foreign key says an order's customer_id must exist in customers, and ON DELETE decides what happens when the parent is removed: block it, cascade the delete, or set the child's value to NULL. NOT NULL means a value must be there, and CHECK enforces a rule, say a score between 0 and 100. I like these in the database because application code has bugs and several apps may write to the same tables. The constraint catches it every time."

"A view is a saved SELECT with a name. A normal view stores no data, so the query runs each time, and I use it to hide a messy join or to give a reporting user only certain columns. A materialized view actually stores the result, so it's fast to read but stale until it's refreshed, and not every engine has them. A user-defined function takes parameters and returns a value or a table, and I can call it inside a SELECT, like a function that formats a product code. Most engines limit what a function is allowed to change. A stored procedure is called on its own, with CALL or EXEC, and can run several statements, write data and often control transactions. I use procedures when logic must sit close to the data, but I keep business rules that change often in application code, where they're easier to test and ship."

"At my last company we had to split a full_name column into first and last name, on a users table with a few million rows that the app wrote to all day. Renaming or dropping the column in one step would have broken the running app. So we did it in stages. First we added the two new columns as nullable, which was quick. Then the app started writing to both the old and new columns. Next I backfilled the old rows in small batches, committing each batch so we never held long locks, and I watched replication lag while it ran. Once the data matched, which I checked with a query comparing counts and a sample of rows, we switched reads to the new columns. We dropped the old column weeks later in its own release, so we could roll back at any point before that."

"An index is a separate sorted structure, usually a B-tree, built on one or more columns. Instead of scanning every row to find one email, the database walks down a few levels of the tree and lands on the right entries, and because it's sorted it also helps range filters and ORDER BY. In engines that use them, a clustered index is the table itself stored in key order, so there's only one. A non-clustered index keeps the key plus a pointer to the row, so the database may need an extra lookup to fetch the other columns. If the index already holds every column the query needs, it's covering and that lookup is skipped. The cost is on writes: each index takes space and must be updated on every insert, update and delete, so I add them for real query patterns, not just in case."

"The index is sorted on the raw created_at values. When I wrap the column in YEAR, the database has to calculate YEAR for every row before it can compare, so it can't jump into the index and it scans instead. The fix is to leave the column bare and move the logic to the other side: created_at on or after the first of January 2025 and before the first of January 2026. That half-open range also handles timestamps with a time part, which BETWEEN with an end date of the thirty-first would miss for anything after midnight. The same thing happens with a LIKE pattern that starts with a wildcard, with maths on the column, and with implicit conversions, like comparing a text column to a number. Some engines let you index the expression itself, but the rewrite is usually simpler."

"I get the plan the engine actually used, not just the estimate. Plain EXPLAIN only shows what the optimizer expects, while the version that really runs the query shows real row counts and time for each step. I read it from the innermost steps outwards, since that's where the rows come from, and find the step that takes most of the time. The first things I check are full scans on big tables where I expected an index, and steps where the estimated rows are far from the actual rows. A bad estimate usually means stale statistics or a filter the optimizer can't reason about, and it leads to wrong choices, like a nested loop that runs thousands of times. I also look for big sorts spilling to disk. Then I fix the biggest cost, re-run it and compare the two plans."

"At my last company a search page timed out for our largest customers. The query joined orders to order_items and filtered by customer and date. I ran it with the actual execution plan and saw a full scan on order_items, and the estimated row counts were tiny compared with the real ones, so the optimizer had picked a plan that was fine for small customers and awful for big ones. Two things were wrong: there was no index on order_items.order_id, and the date filter wrapped the column in a function. I added the index and rewrote the filter as a plain range, and the query went from about forty seconds to under one second for the biggest customer. I checked that inserts into order_items didn't slow down, and added the query to our slow-query alerts so we'd catch it if it drifted again."

"Same SQL doesn't mean same work, so I'd look at what changed around it. First I'd check whether it's slow all the time or only at certain hours, and whether it's just waiting on locks from another job, because then the query itself isn't the problem. Next I'd get the current execution plan and compare it with an older one if the engine keeps plan history. Usually the plan has flipped. Maybe the table grew until the optimizer switched to a scan, the statistics are out of date so the row estimates are wrong, someone dropped an index, or the cached plan was built for an unusual parameter value. Depending on which it is, I'd refresh statistics, put the right index back, or deal with the parameter issue, then confirm with a new plan and timing. Finally I'd add an alert on its run time, so we hear about it before users do."

"Atomicity means the whole transaction happens or none of it does, so I can't take points from A without giving them to B. Consistency means the rules, like a CHECK that a balance never goes below zero, still hold when it's done. Isolation means other transactions don't see my half-finished state. Durability means that once the commit comes back, a crash won't lose it. For the transfer, I begin a transaction and subtract from the sender only where their balance is at least 100, then check that exactly one row was updated. If not, I roll back. Then I add to the receiver and commit. If anything fails in between, I roll back and nothing has changed. Putting the balance check inside the UPDATE avoids a race where two transfers both read the old balance."

"A dirty read is seeing data another transaction changed but hasn't committed, so if it rolls back, I acted on something that never existed. A non-repeatable read is when I read a row, someone commits an update, and my second read of the same row differs. A phantom is re-running a query like all orders today and getting extra rows someone just inserted. The standard levels go Read Uncommitted, which allows all three, Read Committed, which stops dirty reads, Repeatable Read, which also stops non-repeatable reads, and Serializable, which stops phantoms too and behaves as if transactions ran one at a time. Real engines differ: many default to Read Committed, MySQL with InnoDB defaults to Repeatable Read, and some use snapshots rather than locks. So I check the engine's documentation before relying on a level."

"The first question is whether the transaction is still open. If I ran it inside BEGIN, which I always try to do on production, I see the count is wrong and I roll back, and nothing has happened. If it already committed, I stop and tell my lead and whoever's on call straight away, because the sooner people know, the more options we have, and users might be affected right now. Then we work out recovery. If there's point-in-time restore, we restore a copy to a separate database from just before my update and copy the old values back for exactly those rows, rather than restoring over live data. Afterwards I'd write up what happened honestly and push for a habit change: run the WHERE as a SELECT first, check the count, and use a transaction for every manual change."