When a SQL query returns duplicate rows, misses records, produces unexpected totals, or becomes slow, guessing is usually less effective than isolating one symptom at a time. This reusable SQL query debugging checklist shows how to reduce a failing query, verify row counts, investigate joins and NULL values, inspect aggregation and type conversion, and use execution plans to measure performance changes safely.
Overview
SQL bugs generally fall into two categories: a query can return the wrong result, or it can return the right result too slowly. The same statement may have both problems. For example, an unconstrained join can multiply rows and also force the database to process far more data than expected.
Start by writing down the expected behavior in plain language. Identify the intended grain of the result: one row per customer, order, invoice, device, or event. Then compare that expectation with the grain produced by each table and join. A query that is meant to return one row per customer should not silently become one row per customer-and-order unless that change is deliberate.
Use a small, representative data set while debugging. Keep the original query unchanged, make a copy, and change one thing at a time. Record the database engine and version, relevant parameters, indexes, and approximate table sizes. Execution-plan behavior can differ between PostgreSQL, MySQL, and SQL Server, so an explanation that is valid for one dialect may not transfer directly to another.
A useful reduction pattern is:
-- 1. Establish the base population
SELECT COUNT(*) AS base_rows
FROM customers
WHERE status = 'active';
-- 2. Add one join
SELECT COUNT(*) AS after_orders
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.status = 'active';
-- 3. Check whether the join changed the intended grain
SELECT c.customer_id, COUNT(*) AS matching_rows
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id
HAVING COUNT(*) > 1;These checkpoints reveal where the row count changes. Do not begin by adding DISTINCT; first determine whether the additional rows represent valid one-to-many data or an incorrect join.
Checklist by scenario
When joins create duplicate rows
- Confirm the join keys on both sides. A join on a non-unique column, such as a name or status, can match multiple rows unexpectedly.
- Check the relationship: one-to-one, one-to-many, or many-to-many. A customer joined to orders should normally produce one row per matching order, not one row per customer.
- Look for omitted predicates. Composite relationships may require more than one key, such as both
tenant_idandrecord_id. - Inspect duplicate keys directly with
GROUP BY ... HAVING COUNT(*) > 1. - If you need existence rather than columns from the related table, use
EXISTSinstead of a join.
SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.created_at >= :start_date
);Use DISTINCT only when duplicate output rows are genuinely equivalent for the result contract. It can conceal a faulty relationship and may add sorting or hashing work.
When NULL values cause missing or unexpected rows
NULL represents an unknown or absent value; it is not equal to zero, an empty string, or another NULL. Comparisons such as column = NULL do not identify missing values. Use IS NULL or IS NOT NULL:
-- Correct
WHERE shipped_at IS NULL
-- Usually incorrect
WHERE shipped_at = NULLReview outer joins carefully. A condition placed in the WHERE clause can turn a LEFT JOIN into an effective inner join:
-- Keeps customers without qualifying orders
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'open';If o.status = 'open' is placed in WHERE, rows where no order exists have NULL order columns and may be removed. Also check whether COALESCE is hiding a data-quality issue by replacing missing values with a plausible default.
When filtering or aggregation gives the wrong result
- Apply filters at the correct stage. A predicate on raw rows belongs before aggregation; a predicate on an aggregate belongs in
HAVING. - Verify that every selected non-aggregate column is compatible with the
GROUP BYrules of the database. - Count the intended thing.
COUNT(*)counts rows, whileCOUNT(column)excludes NULL values.COUNT(DISTINCT column)counts unique non-NULL values. - Aggregate before joining when a one-to-many table would otherwise multiply facts.
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
)
SELECT c.customer_id, ot.total_amount
FROM customers AS c
LEFT JOIN order_totals AS ot
ON ot.customer_id = c.customer_id;When the query is slow
First establish a baseline: record elapsed time, rows returned, and the parameter values used. Run a representative case rather than only an empty or tiny result. Then inspect the execution plan with the database's explain facility. Prefer an actual plan with runtime and row counts when it is safe and supported, but avoid enabling expensive instrumentation on production traffic without an operational review.
Look for large differences between estimated and actual row counts, scans over unexpectedly large portions of a table, repeated expensive operations, sorts or hashes that spill to disk, and joins performed in an inefficient order. Check whether predicates are searchable through an appropriate index and whether expressions or implicit casts prevent efficient use of that index.
Do not add indexes automatically. An index should support a real access pattern and must be weighed against write cost, storage, and maintenance. After a change, compare the same query, parameters, data state, and plan conditions. A faster test with different cache state is not a reliable conclusion.
What to double-check
Implicit type conversion
Compare data types on both sides of a predicate and join. Joining an integer key to a text representation, or comparing a timestamp column with an incorrectly typed parameter, may cause conversion, prevent index use, or produce surprising matches. Store and bind values using the intended types rather than relying on automatic conversion.
Pagination and ordering
Pagination without a stable ORDER BY is not deterministic. Even with ordering, offset pagination can become costly for deep pages. If the application uses an API or report with changing data, verify that the pagination strategy matches the required consistency. See the API pagination patterns guide for offset, cursor, and link-based approaches.
Dialect differences
PostgreSQL commonly uses EXPLAIN (ANALYZE, BUFFERS) for runtime and I/O details. MySQL uses EXPLAIN and provides additional analysis options depending on version and configuration. SQL Server exposes estimated and actual execution plans through its tooling and supports plan inspection with its own syntax and terminology. Treat plan output as engine-specific, and confirm syntax and safety settings for the installed version before applying advice.
Data and environment
Validate test data against production-like distributions. A query may appear efficient with uniform values but behave differently when one tenant, customer, or status owns most rows. Also check stale statistics, parameter sensitivity, transaction isolation, locks, and concurrent workload before attributing every slowdown to SQL text.
Common mistakes
- Using DISTINCT as a repair: It removes visible duplicates without explaining why they occurred and can hide missing join conditions.
- Changing several things at once: Rewriting joins, adding indexes, and changing predicates together prevents a meaningful before-and-after comparison.
- Testing only with one parameter: Selectivity and plan choice can vary substantially across values.
- Ignoring NULL semantics: Three-valued logic affects comparisons, joins, aggregates, and negated conditions.
- Trusting estimated rows blindly: Compare estimates with actual rows where possible, and investigate statistics or skew when they diverge.
- Optimizing without a correctness test: A faster query is not an improvement if it changes the required result set.
Keep a small regression fixture containing edge cases: a record with no related rows, multiple related rows, NULL values, duplicate-looking keys, boundary timestamps, and empty input. Run the query against this fixture after each rewrite.
When to revisit
Return to this checklist whenever a join is added, a report definition changes, a migration changes data types or constraints, a database upgrade changes optimizer behavior, or a production query begins to slow down. It is also useful before seasonal planning cycles or other periods when data volume and reporting load change.
For a practical review, save the original SQL, expected row grain, test parameters, baseline timing, plan output, and final timing in the change record. Recheck indexes and statistics after substantial data growth. When a tool or database version changes, repeat the reduced test and confirm dialect-specific explain syntax rather than copying an old screenshot or plan interpretation.
Finally, separate correctness approval from performance approval: first prove that the result matches the intended rows and totals, then prove that the query meets its timing and resource goals under representative conditions. This habit turns SQL debugging from trial and error into a repeatable workflow.