SQL Debugging Exercises

Introduction

Bug-hunting practice: queries that look right but return wrong data. Topics: NULL handling, outer joins, GROUP BY pitfalls, datetime mistakes.

Understanding the topic

Core concepts to understand:

• NOT IN with NULL → empty result.
• = NULL never matches.
• LEFT JOIN + WHERE on right side becomes INNER.
• SUM over LEFT JOIN counts NULL as 0 — sometimes wrong.
• Date math across DST.

Syntax reference

Visual workflow / architecture:

Bug class                  Symptom
NULL semantics             Empty / wrong totals
JOIN turning into INNER    Missing rows in report
Sloppy GROUP BY            Bad totals
TIMESTAMP w/o TZ           Off-by-N hours

Real-world use

These bugs hit production routinely. Spotting them quickly is a senior superpower.

Best practices

• Always handle NULL explicitly.
• Push WHERE on right-side cols into ON.
• Use TIMESTAMPTZ.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why does NOT IN with NULL return empty?
• Q2. Find the bug in this LEFT JOIN.
• Q3. GROUP BY pitfalls.
• Q4. Datetime + DST bug.
• Q5. Float vs NUMERIC for money.