SQL Interview Questions for Experienced Engineers, Settled on Two Real Databases

Experienced SQL interviews stop asking what a LEFT JOIN is fairly early. The follow-ups are about behaviour: what a NULL does to an average, what REPEATABLE READ actually repeats, which transaction a deadlock kills. Those have answers you can recite and answers you have seen, and interviewers can tell the two apart.

Everything below was run in two throwaway containers, PostgreSQL 17.11 and MySQL 8.4.11, with two real client sessions wherever a question involves concurrency. Where the engines disagree, both outputs are shown, because "it depends on the database" is only a good answer if you can say how.

Two warm-ups with surprising answers

Can you roll back a TRUNCATE? On PostgreSQL, yes: 1,000 rows, TRUNCATE inside a transaction, ROLLBACK, 1,000 rows. On MySQL, no, and it takes more with it:

MySQL: start transaction; insert audit 'before'; truncate t; insert audit 'after'; rollback;
t = 0 rows     audit = 'before truncate', 'after truncate'    <- the rollback undid nothing

MySQL's manual lists TRUNCATE among statements that "implicitly end any transaction active in the current session, as if you had done a COMMIT". The insert before it was committed, and the insert after it ran in autocommit. PostgreSQL's says the opposite: the truncation "will be safely rolled back if the surrounding transaction does not commit".

What does COMMIT do after an error? On PostgreSQL, one failed statement aborts the transaction; the next insert got SQLSTATE 25P02: current transaction is aborted, and the COMMIT came back with the command tag ROLLBACK. No exception. Code that only checks whether COMMIT threw believes it saved data it did not.

What does COUNT(column) count?

Six employees, one with no salary, two with no bonus:

count(*)  count(1)  count(salary)  count(bonus)  count(distinct salary)
       6         6              5             4                       4

avg(bonus)                      450
sum(bonus) / count(*)           300
sum(salary) where dept = 'hr'   NULL     (no rows; count(*) = 0)

Identical on both engines. COUNT(*) counts rows and COUNT(column) counts non-null values, so AVG(bonus) is 1,800 divided by 4, not 6. If a missing bonus means zero, the correct average is 300, and only avg(coalesce(bonus, 0)) gives it. The aggregate reference adds the other half: "sum of no rows returns null, not zero as one might expect".

And NULL is not even consistent with itself. DISTINCT bonus collapsed the two NULL bonuses into one value, 5 distinct values from 6 rows, yet a self-join on a.bonus = b.bonus matched only the 4 rows with a bonus, because NULL = NULL is not true.

Find the second-highest salary

Salaries 9000, 9000, 7000, 6000, 5000 and one NULL. The popular answer is ORDER BY salary DESC LIMIT 1 OFFSET 1:

order by salary desc limit 1 offset 1                    9000   <- the tie, not the answer
select distinct ... limit 1 offset 1                     7000
max(salary) where salary < (select max(salary) ...)      7000

The trap in the first query is the tie. There is a second one that fewer people know about. Ask PostgreSQL for the highest salary the same way:

PostgreSQL  order by salary desc limit 1              fay | NULL
PostgreSQL  order by salary desc nulls last limit 1        | 9000
MySQL       order by salary desc limit 1                   | 9000
MySQL       ... nulls last                           ERROR 1064 (42000)

PostgreSQL sorts nulls "as if larger than any non-null value; that is, NULLS FIRST is the default for DESC order". MySQL sorts them first in ascending order and has no NULLS LAST syntax at all. max() ignored the NULL on both.

When everyone earns the same, the two correct answers still differ: max ... where salary < max returned one row holding NULL, and the DISTINCT ... OFFSET version returned no rows. Pick the one your caller expects. The same OFFSET is what a paged endpoint generates, with the same sensitivity to ties in the sort key.

Delete duplicates, keeping one

Six rows, three distinct emails. On PostgreSQL, number each group and delete the extras:

delete from users where ctid in (
  select ctid from (
    select ctid, row_number() over (partition by email order by created) as rn from users
  ) t where rn > 1);

DELETE 3    left: a@x.io (created 1), b@x.io (created 4), c@x.io (created 5)

ctid is PostgreSQL's physical row address, which is what you use when the table has no key. The order by decides which copy survives, so it is a decision, not a detail. Then add the constraint that stops it happening again: on a table that still held duplicates, create unique index failed with could not create unique index "u2_email" and DETAIL: Key (email)=(a) is duplicated., and on the cleaned table it succeeded.

The textbook version fails on MySQL:

delete from users where id not in (select min(id) from users group by email)
ERROR 1093 (HY000): You can't specify target table 'users' for update in FROM clause

The manual is explicit: "In general, you cannot modify a table and select from the same table in a subquery." A self-join works and deleted the same 3 rows:

delete u from users u join users k on k.email = u.email and k.id < u.id;

Is EXISTS faster than IN?

Not on either of these engines, and the plan is the proof. 200,000 customers, 1,000,000 orders, "customers with an order over 990":

PostgreSQL  IN      Hash Semi Join   (Seq Scan customers, Hash of Parallel Seq Scan orders)   1800
PostgreSQL  EXISTS  Hash Semi Join   (identical plan, line for line)                           1800
MySQL       IN      Nested loop inner join -> Materialize with deduplication                  1800
MySQL       EXISTS  (identical tree, identical costs)                                          1800
JOIN + count(distinct)  a different plan on both engines: a nested loop driven from orders

Each planner turned IN (subquery) and EXISTS into the same plan, so there is nothing left for one to be faster at. Whatever the rule of thumb was once based on, it did not survive into these two planners. A useful answer is "run EXPLAIN on both", and the difference that still matters is correctness, where NOT IN meets a NULL.

What does REPEATABLE READ protect you from?

The classic lost update: session A reads a balance of 100; session B adds 50 and commits; A writes back the 110 it computed. Run through two real sessions:

                                  PostgreSQL 17                          MySQL 8.4
READ COMMITTED   A reads twice    100, then 150                          100, then 150
REPEATABLE READ  A reads twice    100, then 100                          100, then 100
REPEATABLE READ  A writes 110     ERROR 40001: could not serialize       succeeds; final 110
                                  access due to concurrent update        B's +50 is gone

Both engines give A a stable snapshot for reads. Only PostgreSQL treats the write as a conflict, which its isolation documentation describes: "a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began."

InnoDB goes further than allowing it. With set balance = balance + 10 instead of a computed value, A's UPDATE applied to B's committed 150, and A's next SELECT of that row, still inside the same REPEATABLE READ transaction, returned 160. The same row read 100 and then 160 in one transaction. The InnoDB manual documents it: "The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements."

MySQL's default level is REPEATABLE-READ and PostgreSQL's is read committed. So "our database runs repeatable read" says little until you name the engine.

Common mistake

Reading a value, computing in application code, and writing it back, then trusting the isolation level to make it safe. At READ COMMITTED the committed +50 was lost on both engines (final 110), and at REPEATABLE READ it was still lost on MySQL. The fix that behaved identically on both was SELECT ... FOR UPDATE: B's +50 blocked until A committed, and the final balance was 160.

Two transactions deadlock. Which one dies?

A locks row 1 then wants row 2; B locks row 2 then wants row 1, 100 ms later. Six runs on each engine, the same outcome every time:

PostgreSQL  A (waited first)  SQLSTATE 40P01: deadlock detected    after ~1,005 ms (median of 6)
                              DETAIL: Process 130 waits for ShareLock on transaction 813; blocked by process 131.
            B                 proceeds and commits

MySQL       A                 proceeds and commits
            B (closed cycle)  ERROR 1213 (40001): Deadlock found when trying to get lock;
                              try restarting transaction                 within 18 ms of its request

PostgreSQL's deadlock_timeout was 1s, which matches the roughly one-second wait, and the session that had been waiting longer was the one aborted. InnoDB reported the cycle as soon as B asked for the lock and rolled back B. The victims were opposite.

Do not build on that. PostgreSQL's documentation says: "Exactly which transaction will be aborted is difficult to predict and should not be relied upon." The answer an interviewer wants is that either side can be the victim, so every transaction must be retryable as a whole, and the lasting fix is to lock rows in a consistent order.

The related question is how to run a job queue on a table. Two workers each ran select ... for update skip locked limit 2; one got jobs 1 and 2, the other got 3 and 4, on both engines. A third worker without skip locked blocked until its 2-second lock timeout. That pattern is fine for a job table. Once you need replay and many consumers, you are describing a log-based broker, with its own coordination costs.

Answering these out loud

Each question above has a definition and a consequence, and the consequence is what gets marked. COUNT(column) skips NULLs, so the average is wrong. REPEATABLE READ holds reads still, so on InnoDB it does nothing for a computed write. Deadlock victims are unpredictable, so retry the transaction.

If you are comparing engines, the MySQL and PostgreSQL differences go well beyond syntax, as the isolation table shows. The quickest way to rehearse is to run both databases in containers and open two sessions.

[!TAKEAWAY] Open two sessions before you answer any concurrency question. A REPEATABLE READ that raised 40001 on PostgreSQL and silently lost a committed +50 on MySQL, and deadlock victims chosen in opposite order, are behaviours you remember exactly once you have seen them.