All jobs / Full-stack developer
Databases interview questions
Databases questions a Full-stack 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.
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.
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.
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.
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.
Billing moves `amountCents` from the orders table to a ledger. The SPA must not flicker.
How do you migrate the field without downtime?
The strongest answer: Dual-write both stores, dual-read with a preference for the new, backfill, then stop writing the old — the UI keeps one field name the whole time
Why it works: Exactly. Expand/contract at staff level includes the store and the wire. The UI should not see the construction site.
- Hide the migration behind one contract.
- Dual-write, backfill, dual-read, contract.
- Do not flip the UI before the data exists.
- Schedule the drop; do not collect fields.