SQL Interview Questions and Answers
The eight questions interviewers ask most — answered in plain English with working queries you can copy and practice. Want more practice? SQLGenie turns any question into SQL for free, no account needed.
Beginner
1. What is the difference between INNER JOIN and LEFT JOIN?
The answer
INNER JOIN keeps only the rows that exist in both tables — a customer with no orders simply disappears from the result. LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match, so a customer with no orders still shows up. Interviewers ask this to check you think about which rows survive, not just how tables connect.
Example
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
A classic follow-up: “How do you find customers with NO orders?” — add WHERE o.id IS NULL after the LEFT JOIN.
Beginner
2. What is the difference between WHERE and HAVING?
The answer
WHERE filters rows before any grouping happens, so it cannot use aggregates like SUM or COUNT. HAVING filters groups after GROUP BY, so it can. The execution order is: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Using an aggregate in WHERE is one of the most common interview mistakes.
Example
SELECT customer_id, SUM(total)
FROM orders
WHERE SUM(total) > 1000
GROUP BY customer_id;
SELECT customer_id, SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;
If you can answer “why does WHERE reject SUM?” with the execution order above, you are ahead of most candidates.
Intermediate
3. How do you find duplicate values in a column?
The answer
Group by the column, count the rows in each group, and keep only groups with a count above one. This tests whether you can combine GROUP BY and HAVING without looking it up. A common extension is finding the full duplicate rows, not just the values — that needs a join or a window function on top.
Example
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
SELECT u.*
FROM users u
JOIN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
) dup ON dup.email = u.email;
Mention that ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) is the usual way to keep one copy and delete the rest — it shows you know the cleanup step too.
Intermediate
4. How do you find the second highest salary?
The answer
The textbook answer uses LIMIT with an offset after sorting descending. The trap: two people can share the top salary, so OFFSET can return the same value — using DISTINCT or DENSE_RANK handles ties correctly. Interviewers use this question to see whether you think about edge cases, not just syntax.
Example
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2;
SQL Server: use OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY. Always say out loud what should happen with ties — that is what the interviewer is listening for.
Advanced
5. What is a window function, and how is it different from GROUP BY?
The answer
GROUP BY collapses many rows into one summary row per group — the original rows are gone. A window function computes a value across related rows while keeping every row in the result. ROW_NUMBER, RANK, LAG and SUM(...) OVER (...) are the ones you must know. If a question says “for each row, compare it with others in its group”, that is a window function.
Example
SELECT name,
department,
salary,
RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS dept_rank
FROM employees;
SELECT sale_date,
amount,
amount - LAG(amount) OVER (ORDER BY sale_date) AS day_change
FROM sales;
PARTITION BY works like GROUP BY but without collapsing rows. That one sentence answers the comparison correctly.
Intermediate
6. Why does = NULL not work, and what do you use instead?
The answer
NULL means “unknown”, not a value — and you cannot compare something to unknown, so NULL = NULL is not even true. Every comparison with NULL evaluates to UNKNOWN and the row is filtered out. Use IS NULL and IS NOT NULL. This question also hides a trap: NOT IN with a subquery that returns one NULL silently returns zero rows.
Example
SELECT *
FROM employees
WHERE manager_id = NULL;
SELECT *
FROM employees
WHERE manager_id IS NULL;
SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;
Bonus points for mentioning COALESCE for defaults and NULLIF for turning sentinel values back into NULL.
Beginner
7. What is the difference between DELETE, TRUNCATE and DROP?
The answer
DELETE removes chosen rows, can have a WHERE clause, and is logged row by row so it can be rolled back. TRUNCATE removes all rows at once, is much faster, and usually resets auto-increment counters — but keeps the table. DROP removes the table itself, structure and all. The safe order of destructiveness: DELETE < TRUNCATE < DROP.
Example
DELETE FROM sessions
WHERE expires_at < NOW();
TRUNCATE TABLE staging_import;
DROP TABLE old_reports_2019;
In interviews, add that you would run SELECT with the same WHERE before any DELETE to preview what gets removed — it signals real-world caution.
Advanced
8. What is an index, and when does it make a query faster?
The answer
An index is a sorted lookup structure (usually a B-tree) the database maintains for a column, so it can jump straight to matching rows instead of scanning the whole table. It speeds up WHERE, JOIN and ORDER BY on the indexed columns, but slows down INSERT/UPDATE because every write must also update the index. Indexes stop helping when you wrap the column in a function or match a leading wildcard.
Example
CREATE INDEX idx_orders_customer ON orders (customer_id);
SELECT *
FROM orders
WHERE customer_id = 42;
SELECT *
FROM orders
WHERE YEAR(order_date) = 2026;
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01';
Mention composite indexes follow the leftmost-prefix rule: an index on (a, b) helps WHERE a = … but not WHERE b = … alone.
Practice with your own questions
Type any interview question — or your own table structure — into SQLGenie and get the SQL plus an explanation you can learn from. Free, no account, nothing stored.