SQL Tricky Interview Questions With Answers

Q1: What does NULL = NULL return?

Most candidates answer true. It's actually NULL — full explanation and more examples in NULL Handling in SQL.

NULL means "unknown," and comparing two unknowns can't produce a definite answer — the correct test is IS NULL, never = NULL.

Q2: Find the second-highest salary

The naive answer is ORDER BY salary DESC LIMIT 1 OFFSET 1 — it breaks the moment two employees tie for the highest salary, because both occupy "rank 1" and the true second-highest value gets skipped entirely.

-- Table: employees(id, name, salary) with two people tied at 9000

-- Fragile: OFFSET assumes no ties
SELECT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- Correct: DENSE_RANK handles ties properly
SELECT salary FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

With two people tied at the top salary of 9000, OFFSET 1 just returns the second copy of 9000 — the actual second-distinct-highest value, 8500, never gets picked. DENSE_RANK() ranks by distinct value, so it gets this right. See the full ranking comparison in ROW_NUMBER vs RANK vs DENSE_RANK.

Q3: Why does 10/3 return 3, not 3.33?

When both operands are integers, most databases perform integer division and silently truncate the decimal portion.

SELECT 10 / 3 AS wrong_result;         -- 3
SELECT 10.0 / 3 AS correct_result;    -- 3.333...

Casting one operand to a decimal or float forces floating-point division for the whole expression.

Q4: UNION vs UNION ALL — what actually changes?

UNION removes duplicate rows across both result sets, which requires an implicit sort or hash pass. UNION ALL keeps every row from both sides, duplicates included, and skips that extra work entirely.

If you already know there are no duplicates, or you don't care, UNION ALL is the faster default — reach for plain UNION only when de-duplication is actually required.

Q5: Why does a self-join double-count duplicates?

A classic "find duplicate rows" self-join uses a.id <> b.id to exclude a row matching itself — but that condition still matches pair (A, B) and pair (B, A) separately, doubling the count.

-- Wrong: counts every pair twice
SELECT a.email FROM customers a
JOIN customers b ON a.email = b.email AND a.id <> b.id;

-- Correct: directional comparison counts each pair once
SELECT a.email FROM customers a
JOIN customers b ON a.email = b.email AND a.id < b.id;

Q6: Why does COUNT(*) disagree with COUNT(column)?

COUNT(*) counts every row regardless of content. COUNT(column) counts only rows where that specific column is not NULL — the two only match when the column has no NULLs at all. Full breakdown in COUNT(*) vs COUNT(column).

Q7: Why did an INNER JOIN drop rows you expected?

INNER JOIN only keeps rows with a match on both sides — a customer with zero orders disappears entirely, which surprises candidates expecting every customer to show up. The fix is usually a LEFT JOIN, covered with visuals in INNER JOIN vs LEFT JOIN vs RIGHT JOIN.

Q8: Why does ORDER BY put NULLs somewhere unexpected?

NULL's sort position isn't universal — PostgreSQL sorts NULLs last by default on an ascending sort, while MySQL sorts them first. Relying on default behavior across databases is a common portability trap; ORDER BY column NULLS LAST (where supported) makes the behavior explicit instead of implicit.