SQL Query Debugging Checklist: Find Duplicate Rows, Null Errors, and Slow Joins

Overview

SQL query debugging starts by separating the symptom from the cause. A duplicated result may come from a one-to-many join rather than duplicate source data. Missing rows may be caused by a filter on the nullable side of a LEFT JOIN. A slow query may need an index, but it may also be reading far more rows than intended because a predicate is too broad.

Before changing production SQL, capture the query, parameters, database engine, relevant table sizes, and the expected result. Run a reduced version against a safe environment when possible. Format the statement first so joins, filters, grouping, and ordering are visible; a SQL formatter guide can help make a long query easier to inspect.

Checklist by scenario

When a join returns duplicate rows

First establish the intended grain: one row per customer, order, invoice, or another business entity. Then check whether every join preserves that grain. This query identifies keys that match multiple rows:

SELECT customer_id, COUNT(*) AS matching_rows
FROM customer_addresses
GROUP BY customer_id
HAVING COUNT(*) > 1;

Repeat the check for each join key. If a customer can have several addresses, joining customers to addresses naturally produces several rows. That is not a database error; it is a mismatch between the query's join shape and the requested output.

To locate the join that multiplies results, count progressively:

SELECT COUNT(*) AS base_rows FROM customers;

SELECT COUNT(*) AS joined_rows
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

SELECT COUNT(*) AS further_joined_rows
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id;

Use a more specific condition when the relationship requires it. Avoid adding DISTINCT as a first response: it can hide a faulty join and remove legitimately different rows. If you need one related row, state which one, such as the latest address, and select it with a window function or engine-appropriate subquery.

When rows are missing

Temporarily change an INNER JOIN to a LEFT JOIN and look for unmatched records:

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

This distinguishes “there is no related row” from “the earlier query filtered it out.” Pay particular attention to conditions on the right-hand table. This preserves unmatched customers:

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

Moving o.status = 'paid' into the WHERE clause usually removes rows where o.status is NULL, making the outer join behave like an inner join for that condition.

When NULL values behave unexpectedly

NULL means unknown or absent, not an ordinary value. Comparisons such as column = NULL and column <> NULL do not test for NULL. Use:

SELECT * FROM payments WHERE processed_at IS NULL;
SELECT * FROM payments WHERE processed_at IS NOT NULL;

Also check whether a calculation becomes NULL because one input is NULL. Use COALESCE only when a replacement value is logically correct:

SELECT order_id,
       COALESCE(discount_amount, 0) AS discount_amount
FROM orders;

Be cautious with NOT IN when its subquery can return NULL. A NOT EXISTS query often expresses the intended anti-join more clearly:

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

When totals are too high

Aggregation after a one-to-many join can count the same parent value repeatedly. For example, joining orders to items before summing an order-level shipping amount may add shipping once per item. Aggregate at the correct grain first:

WITH item_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS item_total
  FROM order_items
  GROUP BY order_id
)
SELECT o.customer_id, SUM(i.item_total) AS revenue
FROM orders o
JOIN item_totals i ON i.order_id = o.order_id
GROUP BY o.customer_id;

Check every selected column against the grouping rule supported by your database. A query that groups by customer but selects an unaggregated order attribute may be rejected, or may produce an arbitrary result in permissive configurations.

When a query is slow

Reduce the problem before optimizing it. Select only needed columns, remove optional joins, narrow the time range, and test the query without sorting or pagination layers that are not relevant to the suspected issue. Compare the estimated and actual number of rows at each plan step.

Use the optimizer tools available in your database:

• PostgreSQL: EXPLAIN shows the planned strategy. EXPLAIN (ANALYZE, BUFFERS) executes the statement and reports actual timing and buffer activity, so use it carefully for writes and production workloads.
• MySQL: EXPLAIN inspects the selected plan; supported releases may also provide EXPLAIN ANALYZE for execution details. Check the documentation for the server version in use.
• SQL Server: inspect the estimated or actual execution plan, and use SET STATISTICS IO ON and SET STATISTICS TIME ON when appropriate.
• SQLite: EXPLAIN QUERY PLAN provides a compact view of scans, searches, and indexes.

Look for full scans on large tables, repeated scans inside nested loops, large differences between estimated and actual row counts, expensive sorts, and filters applied after a large intermediate result. An index can help a selective predicate or join key, but index design depends on data distribution, write workload, column order, and the actual plan. Do not add indexes solely because a column appears in a query.

What to double-check

1. Parameters and types: Test with the same parameter values and types used by the application. A plan that works for a selective value may perform differently for a common value.
2. Time boundaries: Decide whether ranges are inclusive or exclusive. Half-open ranges such as created_at >= :start AND created_at < :end avoid many timestamp boundary problems.
3. Collation and case: Text comparison, sorting, and index use can vary with database configuration.
4. Conversion and functions: Applying a function or implicit cast to an indexed column may prevent efficient index use, depending on the engine.
5. Statistics and freshness: If estimates are consistently wrong, check whether table statistics or database maintenance need attention according to your engine's practices.
6. Pagination: An expensive offset can require the database to process and discard many rows. For large, changing result sets, evaluate keyset or cursor pagination; see the pagination strategy guide.
7. API expectations: Confirm that serialization, filtering, and pagination in the application are not changing the result after SQL execution. Formatting the response with a JSON formatter for API debugging can make that comparison easier.

Common mistakes

• Using DISTINCT to conceal a many-to-many join instead of fixing the relationship or defining the required grain.
• Adding an index for every filtered column without checking composite index order and write overhead.
• Comparing a query run in a development database with a production plan without considering data volume and distribution.
• Using SELECT * in a report or API query, which increases transferred data and makes schema changes more disruptive.
• Reading an estimated plan as proof of actual performance. Estimates are useful clues, but actual execution details are needed when available.
• Testing only a successful case. Include records with no children, NULL attributes, duplicate-looking keys, boundary timestamps, and unusually large groups.

When to revisit

Return to this checklist whenever a report changes, a migration adds or removes a column, data volume grows, an index is modified, or an API begins returning different counts. Recheck queries before seasonal planning cycles or other predictable traffic changes, especially when date filters and aggregation are involved.

Keep a small regression set with expected row counts and representative edge cases. Record the query's intended grain, important indexes, representative parameters, and the plan observations that justified an optimization. After changing SQL or indexes, compare correctness first, then execution time, logical reads, and resource use in a representative environment.

A practical final pass is: format the query, state its expected grain, count rows after each join, test NULL and unmatched cases, inspect the execution plan, and document the reason for every corrective change. This turns SQL query debugging from trial and error into a workflow you can repeat when the underlying data or application changes.