SQL DEBUGGING PLAYBOOK
How to Diagnose, Fix & Optimize SQL Queries Like a Data
Engineer
25 Real Query Debugging Scenarios
Fix Incorrect Results · Resolve Performance Issues · Identify Hidden Bugs
Version 1.0 · Elevate Space · Text-Only PDF
LEGAL & USAGE
This material is for educational purposes only.
No guarantees of interview outcomes or job placement are made.
Personal license for the buyer.
Do not resell or redistribute this material.
Examples are fictional but based on real SQL debugging patterns used in
production systems and technical interviews.
HOW TO USE THIS PLAYBOOK
Debugging SQL is one of the most valuable skills in data roles.
Most real-world SQL work involves:
fixing incorrect queries
• debugging slow queries
• identifying incorrect joins
• handling NULL and duplicates
• optimizing large dataset queries
Each scenario in this playbook contains:
Untitled 1
1️⃣ Broken query
2️⃣ Problem explanation
3️⃣ Correct solution
4️⃣ Debugging reasoning
5️⃣ Prevention tip
THE SQL DEBUGGING FRAMEWORK
Before fixing any SQL query, follow this systematic process.
Step 1 — Understand the Expected Output
Ask:
What should the query return?
• What columns should appear?
• What business metric is being calculated?
Many SQL bugs happen because the expected result is unclear.
Step 2 — Check Table Relationships
Incorrect joins are the #1 cause of wrong SQL results.
Ask:
Are the join keys correct?
• Is it a one-to-many relationship?
• Should it be INNER JOIN or LEFT JOIN?
Step 3 — Validate Filtering Logic
Common mistakes include:
filtering after aggregation incorrectly
• missing filters
• incorrect WHERE conditions
Step 4 — Verify Aggregations
Untitled 2
Aggregation bugs happen when:
columns missing in GROUP BY
• incorrect aggregation level
• duplicate rows inflate totals
Step 5 — Test Edge Cases
Always check:
NULL values
• duplicates
• missing records
SECTION 1 — COMMON SQL LOGIC BUGS
Debug Scenario 1 — Incorrect NULL
Comparison
Broken Query
SELECT *
FROM employees
WHERE manager_id = NULL;
Problem
NULL values cannot be compared using = .
The query will return zero rows even if NULL values exist.
Correct Query
SELECT *
FROM employees
Untitled 3
WHERE manager_id IS NULL;
Debugging Reasoning
SQL uses three-valued logic:
TRUE
FALSE
UNKNOWN (NULL)
= NULL always evaluates to UNKNOWN.
Prevention Tip
Always use:
IS NULL
IS NOT NULL
Debug Scenario 2 — Duplicate Rows From
Incorrect Join
Broken Query
SELECT c.customer_id, o.order_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
This query unexpectedly returns duplicate customer rows.
Problem
A customer may have multiple orders, creating multiple rows.
Untitled 4
Correct Query
If only unique customers are needed:
SELECT DISTINCT c.customer_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Debugging Reasoning
This is a one-to-many relationship.
Each order multiplies customer rows.
Prevention Tip
Always ask:
Is the join one-to-many or one-to-one?
Debug Scenario 3 — Aggregation Error
Broken Query
SELECT department, salary
FROM employees
GROUP BY department;
Problem
salary is not aggregated and not included in GROUP BY.
This causes an error in most SQL systems.
Correct Query
Untitled 5
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Debugging Reasoning
When GROUP BY is used:
All selected columns must either be:
aggregated
• included in GROUP BY
Prevention Tip
Always verify aggregation logic before writing SELECT columns.
SECTION 2 — JOIN DEBUGGING
Debug Scenario 4 — Missing Join Condition
Broken Query
SELECT *
FROM customers
JOIN orders;
Problem
Missing join condition creates a Cartesian product.
If customers = 1000 rows and orders = 10,000 rows:
Result = 10 million rows.
Untitled 6
Correct Query
SELECT *
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Debugging Reasoning
Without join condition SQL pairs every row with every row.
Prevention Tip
Always verify join conditions before running queries.
Debug Scenario 5 — Incorrect LEFT JOIN
Filtering
Broken Query
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NOT NULL;
Problem
The WHERE clause converts the LEFT JOIN into an INNER JOIN.
Correct Query
If you want customers with orders:
Untitled 7
SELECT c.customer_id
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;
Debugging Reasoning
Filtering after LEFT JOIN removes NULL rows.
Prevention Tip
Understand the difference between:
WHERE
JOIN condition
SECTION 3 — WINDOW FUNCTION
DEBUGGING
Debug Scenario 6 — Incorrect Ranking
Broken Query
SELECT employee_id,
salary,
RANK() OVER (ORDER BY salary)
FROM employees;
Problem
Ranking should be descending for highest salary first.
Untitled 8
Correct Query
SELECT employee_id,
salary,
RANK() OVER (ORDER BY salary DESC)
FROM employees;
Debugging Reasoning
ORDER BY direction affects ranking.
Prevention Tip
Always verify whether ranking should be:
ascending
• descending
SECTION 4 — PERFORMANCE DEBUGGING
Debug Scenario 7 — Slow Query Due to
Function in WHERE
Broken Query
SELECT *
FROM orders
WHERE YEAR(order_date) = 2025;
Problem
Applying a function prevents index usage.
Untitled 9
Correct Query
SELECT *
FROM orders
WHERE order_date BETWEEN '2025-01-01'
AND '2025-12-31';
Debugging Reasoning
Functions disable index optimization.
Prevention Tip
Avoid applying functions to indexed columns.
Debug Scenario 8 — Missing Index
Slow Query
SELECT *
FROM orders
WHERE customer_id = 100;
Optimization
CREATE INDEX idx_customer_id
ON orders(customer_id);
Debugging Reasoning
Indexes reduce full table scans.
SECTION 5 — ADVANCED DEBUGGING
Untitled 10
Debug Scenario 9 — Incorrect Running
Total
Broken Query
SELECT order_date,
SUM(order_amount)
FROM orders;
Problem
This calculates total revenue, not running total.
Correct Query
SELECT order_date,
SUM(order_amount)
OVER (ORDER BY order_date)
FROM orders;
Debug Scenario 10 — Detecting Hidden
Duplicate Rows
Query
SELECT order_id
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;
Untitled 11
Purpose
Identify duplicate records caused by:
bad ETL pipelines
• duplicate imports
SQL DEBUGGING CHECKLIST
Before finalizing any query:
✔ Verify join relationships
✔ Check NULL handling
✔ Confirm aggregation level
✔ Validate filters
✔ Test with small datasets
✔ Review execution plan
FINAL NOTE
Writing SQL queries is important.
But debugging SQL queries is what separates junior analysts from senior
engineers.
Mastering debugging skills will help you:
fix incorrect reports
• optimize analytics dashboards
• solve production data issues
• pass technical interviews
Untitled 12