Debug SQL query errors and incorrect row counts

Find syntax errors, incorrect joins, and silent null filter bugs in database queries. Use it when a query returns syntax errors or unexpected row counts.

You getRoot Cause Analysis, corrected SQL Query and explanation of Changes

Prompt

Prompt
Act as a principal database administrator and SQL expert. Analyze the SQL query, schema context, and problem description pasted below.
- Identify the exact root cause of the error or unexpected row count, checking joins, WHERE clauses, NULL handling, and GROUP BY rules.
- Provide an optimized, corrected SQL query adhering to standard SQL conventions.
- Explain precisely why the original query misbehaved and how your change fixes it.
- Provide a verification query to run against sample data to validate correctness.
Output format:
1. Root Cause Analysis (bullet points)
2. Corrected SQL Query (fenced code block)
3. Explanation of Changes (numbered list)
4. Verification Query (code block)

Here is the query, database dialect, and the issue:

Example output

Example for: PostgreSQL query with duplicate rows caused by a 1-to-many join and NULL filter issue

1. Root Cause Analysis
  • Joining the orders table directly to the order_items table on a one-to-many relationship causes rows from the orders table to duplicate for every item matching the order.
  • The condition status != 'cancelled' discards rows where status is NULL because SQL uses three-valued logic; NULL comparisons evaluate to UNKNOWN.
2. Corrected SQL Query
SELECT 
    o.id AS order_id,
    o.customer_id,
    o.created_at,
    COALESCE(SUM(oi.price * oi.quantity), 0) AS total_amount
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE (o.status IS NULL OR o.status != 'cancelled')
GROUP BY 
    o.id,
    o.customer_id,
    o.created_at;
3. Explanation of Changes
  1. Added COALESCE to handle orders with zero line items, returning 0 instead of NULL for total calculations.
  2. Replaced the strict inequality with (o.status IS NULL OR o.status != 'cancelled') to avoid silently dropping rows where status is not populated.
  3. Added explicit GROUP BY across non-aggregated columns to collapse line-item rows into a single row per order.
4. Verification Query
SELECT order_id, COUNT(*)
FROM (
    SELECT o.id AS order_id
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE (o.status IS NULL OR o.status != 'cancelled')
    GROUP BY o.id, o.customer_id, o.created_at
) sub
GROUP BY order_id
HAVING COUNT(*) > 1;
Written by AI from this prompt. Your results will vary.

Paste your broken SQL query along with your database dialect (e.g. PostgreSQL, MySQL, Snowflake) into ChatGPT, Claude, or Gemini. Ask to add indexes or performance recommendations as a follow-up.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Coding