SQL interview questions and answers for joins, performance and transactions
SQL interviews test whether you can preserve the meaning of data while making a query correct and explainable. The best answer says which rows should exist after each step, how NULL changes the result, and how you would measure performance rather than guessing. The examples use PostgreSQL terminology where it is useful and follow its table-expression documentation.
How SQL questions appear in engineering interviews
You may write a query from a small schema, diagnose a slow report, or explain a production incident caused by a migration or lock. Read the result set aloud before writing SQL. That discipline also helps in code review, system design, and a technical phone screen.
Junior SQL interview questions
1. What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN returns rows that have a matching row on both sides of the join condition. A LEFT JOIN preserves every row from the left side and fills right-side columns with NULL when there is no match. Choose based on whether unmatched left rows belong in the answer.
2. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation. For example, filter inactive orders in WHERE, then use HAVING to keep customers whose remaining order count exceeds a threshold.
3. How does NULL behave in SQL?
NULL means unknown or absent, not an ordinary value. Comparisons with NULL are unknown, so use IS NULL or IS NOT NULL, not equality. Be explicit about whether a missing value should be excluded, preserved or replaced.
4. What does GROUP BY do?
It partitions rows into groups so aggregates such as COUNT, SUM and AVG can produce one result per group. Every selected expression must either be grouped, aggregated, or be functionally dependent according to the database rules.
5. What is the difference between UNION and UNION ALL?
UNION removes duplicate rows, which requires extra work. UNION ALL simply concatenates results and is usually the right choice when duplicates are meaningful or already impossible.
Mid-level SQL interview questions
6. What is a common table expression?
A CTE is a named query introduced with WITH. It can make a multi-step query easier to read, support recursion, or give a logical intermediate result a name. It is not automatically a performance optimisation; inspect the plan for the database and version you use.
7. What is a window function?
A window function calculates over related rows without collapsing them into one group. For example, ROW_NUMBER() can number orders per customer and SUM(amount) OVER (...) can compute a running total while keeping every order row visible.
8. How do you find duplicate values?
Group by the columns that define uniqueness and keep groups with COUNT(*) > 1. Before deleting anything, identify which row should survive and why; a duplicate business key is often a data-model or race-condition problem, not only a cleanup query.
9. What does an index do?
An index is an additional data structure that can make certain reads faster. It costs storage and makes writes more expensive because the index must change too. Choose indexes from real query predicates, joins and ordering, then verify the plan.
10. Why can OFFSET pagination become slow?
For a deep page, the database may still need to walk and discard many earlier rows. Keyset pagination uses a stable ordered cursor such as the last seen timestamp and ID, which is often more predictable for large, frequently changing lists.
Senior SQL interview questions
11. How do transactions help?
A transaction groups related changes so they commit together or roll back together. The key interview follow-up is the isolation level: ask what anomalies are acceptable and whether concurrent readers and writers need stronger guarantees.
12. What causes a deadlock?
A deadlock occurs when transactions hold locks that each other need and neither can proceed. Keep transactions short, access shared resources in a consistent order, and handle a database deadlock error with a safe retry where the operation is idempotent.
13. How do you use EXPLAIN?
Use EXPLAIN to see the planner's chosen operations and estimated costs; use the database's analysed form in a safe environment to compare estimates with actual rows and timings. Look for an unexpected scan, bad cardinality estimate, expensive sort or join, then change one hypothesis at a time.
14. How do joins create duplicate-looking results?
A join returns every matching combination. If one customer has many orders and many support tickets, joining both child tables directly can multiply rows. Aggregate one side first or choose a query shape that matches the intended cardinality.
15. How do you prevent SQL injection?
Use parameterised queries or the database driver's bind variables; never build a query by concatenating untrusted values. Dynamic identifiers need a separate allowlist because parameters protect values, not table or column names.
How to narrate a SQL solution
First describe the rows you need. Then state joins, filters, grouping and ordering in that order. Finally name an edge case: NULL, duplicate cardinality, time zones, concurrency or an empty result. Practise speaking it before writing; that makes the query easier to review and easier to explain during a live coding answer.
Ask the recruiter what database dialect and tools are permitted. Syntax differs; the data reasoning should not.
Sources
- PostgreSQL documentation — official documentation
FAQ
What SQL interview questions are most common?
Joins, NULL, WHERE versus HAVING, GROUP BY, CTEs, window functions, indexes, query plans, transactions, deadlocks, pagination and SQL injection are common themes.
How do I explain a LEFT JOIN?
A LEFT JOIN keeps every row from the left table and uses NULL for columns from the right table when no row matches the join condition. Say whether unmatched left rows belong in the expected result.
When should I use a window function?
Use a window function when you need an aggregate or ranking across related rows while keeping each original row, such as ranking orders per customer or computing a running total.
How do I approach a slow SQL query?
Confirm the expected result and input size, inspect the query plan, compare estimated and actual rows where safe, then test one change such as an index, join order or query rewrite.
What should I clarify before writing SQL in an interview?
Clarify the expected grain of the result, duplicate rules, NULL meaning, time-zone assumptions, ordering, scale and the SQL dialect.