Databases interview questions
Databases questions a Backend developer can be asked, from first job to senior. Open one to see the strongest answer and why it works.
What is the difference between an INNER JOIN and a LEFT JOIN?
The strongest answer: INNER returns only rows that match on both sides; LEFT returns every row from the left table and fills the right side with NULLs where there is no match — so LEFT is how you keep the customers with no orders
Why it works: Right. The question to ask is: do I want rows with no match to survive?
- INNER: matches on both sides only.
- LEFT: all left rows, NULLs for missing right.
- Use LEFT to keep the ones without a match.
- A WHERE on the right table can silently turn LEFT into INNER.
Why does WHERE status != 'closed' not return rows where status is NULL?
The strongest answer: Because NULL is unknown, and any comparison with NULL is neither true nor false — the row is filtered out; use IS NULL or COALESCE to include them deliberately
Why it works: Exactly. Three-valued logic: comparisons with NULL are unknown and WHERE keeps only true.
- NULL means unknown, not empty.
- Comparisons with NULL are unknown, so WHERE drops them.
- IS NULL / IS NOT NULL to test.
- COALESCE to give NULL a stand-in value.
When do you use HAVING instead of WHERE?
The strongest answer: HAVING filters after grouping, on aggregates — customers with more than five orders; WHERE filters rows before they are grouped
Why it works: Right. WHERE first on rows, GROUP BY, then HAVING on the groups.
- WHERE: rows, before grouping.
- HAVING: groups, after aggregation.
- COUNT, SUM, AVG live in HAVING.
- Order: WHERE → GROUP BY → HAVING.
What is a primary key, and what is a foreign key?
The strongest answer: A primary key uniquely identifies each row and cannot be NULL; a foreign key is a column that references another table's primary key, and the database enforces that the referenced row exists
Why it works: Exactly. Identity on one side, an enforced reference on the other.
- Primary key: unique, not NULL, identifies the row.
- Foreign key: references a primary key elsewhere.
- Referential integrity is enforced by the database.
- Keys are constraints; indexes are structures.
What does an index do, and what does it cost?
The strongest answer: It lets the database find rows by a column without scanning the whole table, like a book's index; it costs storage and makes every insert, update and delete on that column slower because the index must be maintained
Why it works: Right. Faster reads on that column, a tax on every write.
- Index: fast lookup on a column.
- Costs storage and write time.
- Index what you filter and join on.
- Every index is maintained on every write.
What is the difference between LIKE 'smith%' and LIKE '%smith'?
The strongest answer: The first matches values starting with smith and can use an index; the second matches values ending with smith and must scan every row because an index cannot help with a leading wildcard
Why it works: Right. A leading wildcard throws away the index.
- % matches any characters.
- Prefix patterns can use an index.
- Leading % forces a scan.
- Full-text search for real text queries.
A report shows duplicate customers. Is SELECT DISTINCT the fix?
The strongest answer: Usually not — duplicates from a join mean the join or the grouping is wrong; DISTINCT hides the symptom and costs a sort, so find why rows multiply first
Why it works: Right. DISTINCT on a wrong join is a bandage over a bug.
- Ask why rows are duplicated.
- A one-to-many join multiplies rows.
- DISTINCT hides the cause and costs a sort.
- Aggregate or fix the join instead.
The interviewer asks: “Do you write SELECT * in production queries?” Strongest answer?
The strongest answer: I select the columns I need. SELECT * ships unused data, breaks callers when the table grows, and fights covering indexes.
Why it works: Exactly. Name the columns. It's documentation, a smaller payload, and a schema change won't surprise a serializer.
- Name the columns you return.
- SELECT * couples callers to every future column.
- Smaller rows are easier to index.
- Never let the client choose SQL identifiers.
What does ORDER BY ... LIMIT 10 without an index on the sort column cost?
The strongest answer: The database must read every matching row and sort them all before it can take ten; with an index on the sort column it can walk the index and stop after ten
Why it works: Right. Sort first, then limit — unless the index already provides the order.
- ORDER BY before LIMIT.
- Without an index: sort everything.
- Index on the sort column: stop after N.
- Top-N queries want the index.
The interviewer asks: “You need each order and its customer name. How do you load that?” Strongest answer?
The strongest answer: JOIN (or a single query the ORM can emit) on the foreign key so one round-trip returns both. Don't query customers inside a loop of orders.
Why it works: Exactly. That loop is the N+1 you will meet in the next band. One join (or a well-batched IN) is the junior-level fix.
- Join (or batch) on the foreign key.
- One round-trip for the graph you need.
- Don't query inside a foreach.
- Don't download both tables to join in RAM.
What is the difference between COUNT(*) and COUNT(email)?
The strongest answer: COUNT(*) counts rows; COUNT(email) counts rows where email is not NULL, so the two differ whenever the column has NULLs
Why it works: Right. Column counts skip NULLs; star counts rows.
- COUNT(*): rows.
- COUNT(col): non-NULL values.
- COUNT(DISTINCT col): distinct non-NULL values.
- NULLs are the difference.
The interviewer asks: “What is a database index for?” Strongest answer?
The strongest answer: An index is a lookup structure on a column (or columns) so WHERE and JOIN don't scan the whole table. I index what I filter and join on — not every column.
Why it works: Exactly. Think of a book index: you pay write cost to avoid a full scan on the lookups you actually do.
- Indexes speed the lookups you actually run.
- Match them to WHERE, JOIN and ORDER BY.
- They cost writes and space.
- Don't add or drop them by vibe.
UNION versus UNION ALL — which do you reach for by default?
The strongest answer: UNION ALL, unless you need duplicates removed: UNION deduplicates, which means a sort or hash over the whole result, and most of the time the two sides cannot overlap anyway
Why it works: Right. ALL is cheaper; use plain UNION only when duplicates are a real possibility you want gone.
- UNION: deduplicates, costs a sort.
- UNION ALL: keeps everything, cheap.
- Default to ALL.
- Same column count and compatible types.
The interviewer asks: “Primary key versus foreign key?” Strongest answer?
The strongest answer: A primary key uniquely identifies a row in this table. A foreign key points at a primary (or unique) key in another table and keeps that relationship honest.
Why it works: Exactly. PK = identity here. FK = a real reference elsewhere, enforced by the database so orphans can't land.
- PK identifies the row; FK references another row.
- Declare both in the schema.
- The database enforces the relationship.
- Don't “speed up deploys” by dropping integrity.
What does a transaction guarantee, in plain words?
The strongest answer: That a group of statements either all take effect or none do — a transfer debits one account and credits another or does neither — and that once committed the change is durable
Why it works: Right. All or nothing, and permanent once committed.
- All statements commit or none do.
- Committed means durable.
- Wrap multi-step changes in one transaction.
- Keep transactions short.
Why must user input go into a query as a parameter rather than pasted into the string?
The strongest answer: Because a parameter is sent as data and can never change the query's structure; string concatenation lets ' OR 1=1 -- become part of the SQL, which is SQL injection
Why it works: Exactly. Parameters keep data as data.
- Never concatenate user input into SQL.
- Parameters separate data from structure.
- Applies to every input and type.
- ORMs parameterise for you — until you write raw SQL.
The interviewer asks: “What is a database transaction for?” Strongest answer?
The strongest answer: All or nothing: either every write in the unit commits, or none do. I open it around the business unit — debit and credit together — not around a single INSERT I could do without.
Why it works: Exactly. Atomicity is the junior contract. The unit is the business action, not the line of SQL.
- A transaction is all-or-nothing.
- Wrap the whole business unit.
- One statement doesn't need a ceremony.
- Never keep going after a failed sibling write.
The interviewer asks: “When does an index actually get used?” Strongest answer?
The strongest answer: When the leading columns match the filter or join, the predicate is sargable (no function wrapping the column), and the planner prefers it to a scan. I confirm with EXPLAIN, not hope.
Why it works: Exactly. WHERE YEAR(created) = 2026 won't use an index on created. Leading-column order, selectivity, and the plan are the job.
- Index use is a plan, not a decoration.
- Keep predicates sargable.
- Match leading columns to the filter.
- Read EXPLAIN; don't hint by default.
The interviewer asks: “Two tabs save the same order. How do you not silently overwrite?” Strongest answer?
The strongest answer: A version column or ETag. UPDATE … WHERE id AND version = N; 0 rows means 409 Conflict. Last-write-wins is a product decision, not the default.
Why it works: Exactly. Optimistic concurrency is the 1–3 standard: cheap, no long locks, honest 409 so the UI can merge.
- Version or ETag on writes.
- 0 rows updated → 409, not a silent win.
- Don't lock the whole system.
- Don't retry by clobbering.
The interviewer asks: “How do you enforce 'one open cart per user'?” Strongest answer?
The strongest answer: A unique constraint (or unique filtered index) in the database. The app can check first for a nicer error, but two concurrent posts will race — the constraint is the real lock.
Why it works: Exactly. Uniqueness is a data invariant. Only the database can close the race under concurrency.
- Uniqueness lives in the database.
- Check-then-act races.
- Optional app check for UX; constraint for truth.
- Don't defer integrity to a cron.
The interviewer asks: “The list query still hits the table after you indexed status. What might you do?” Strongest answer?
The strongest answer: Include the selected columns in the index (INCLUDE / covering) so the query is index-only — if this query is hot and the extra write cost is worth it. Confirm with the plan.
Why it works: Exactly. A covering index pays write and storage cost to avoid heap lookups. Use it on a measured hot path, not on every table.
- Covering = index holds the selected columns.
- Use it on a hot, measured query.
- Narrow SELECT first.
- Don't duplicate indexes or jump to Redis.
The interviewer asks: “When do you partition a table?” Strongest answer?
The strongest answer: When a time or tenant key in the WHERE of hot queries lets the planner prune, and ops needs drop/archive by that key. I don't partition because the table “feels big.”
Why it works: Exactly. Partitioning is an ops and pruning tool. The partition key must appear in the queries you care about, or you scan every partition.
- Partition on a key you prune and archive.
- Size alone is not the trigger.
- Unfiltered hash partitions rarely help.
- Keep indexes; partitions don't replace them.
The interviewer asks: “One tenant is 40% of a sharded table. What's the problem?” Strongest answer?
The strongest answer: A hot partition / hotspot: that shard's CPU, locks and IOPS dominate. I split the key (time buckets, hash suffix, isolate the whale) — I don't add more shards that still hash to the same tenant.
Why it works: Exactly. Sharding only helps if the key distributes. A single popular key needs a different split, not more clones of the same hotspot.
- Hot keys don't shard themselves.
- Split or isolate the whale.
- More shards with the same key won't help.
- Don't “fix” load by deleting data.
The interviewer asks: “Two requests read a balance of 100 and both write 90. What happened, and what do you do?” Strongest answer?
The strongest answer: A lost update under read-committed: both based the write on a stale read. I prevent it with optimistic versioning, SELECT FOR UPDATE in a short transaction, or a single atomic UPDATE … SET bal = bal - 10 WHERE bal >= 10.
Why it works: Exactly. Lost update is the classic 3–5 concurrency bug. Prefer an atomic increment or a version check; pessimistic locks if the critical section is tiny.
- Lost update = two reads, two writes, one disappears.
- Atomic UPDATE or a version/ETag.
- Short FOR UPDATE if you must lock.
- Don't global-serialisable as a personality.