How to Review AI-Generated SQL Before You Trust the Number
An AI assistant will write you a query in ten seconds, the query will run, and the number that comes back will look completely reasonable. This page gives you the five checks that tell you whether that number is right. They take about two minutes, they need no tools beyond the database you already have, and they catch the four mistakes AI-written SQL actually makes. The order matters. The checks…
AI-generated SQL can produce results that look reasonable at first glance, even if they are fundamentally incorrect. To ensure the accuracy of the numbers, it is essential to perform a few simple checks before trusting the output. These checks take only a few minutes and require no additional tools beyond the database itself. The checks are arranged in order of increasing complexity, with the first one costing just a single row count and the last one requiring a brief conversation.
The first check involves counting the rows before trusting the sum. For instance, consider a query calculating the net revenue from completed orders using a LEFT JOIN from orders to refunds. The query returns 1,830, but the correct answer is actually 1,330. The discrepancy arises because two refunded orders each match two refund rows, causing the join to expand the row count from eleven to thirteen. This phenomenon is known as fan-out and can lead to double-counting of certain values.
The second check focuses on looking for NULL values in every filter condition. In a second query attempting to calculate the same revenue while excluding staff accounts, the assistant's query returned NULL due to one NULL value in the staff_accounts table. This happened because the NOT IN operator cannot determine whether any value differs from NULL. To avoid this issue, either remove NULL values from the list or use the NOT EXISTS operator, which does not have this behavior.
The third check involves asking where the filter sits in the query – either in the WHERE clause or the HAVING clause. For a query requesting the customers who spent more than 400 on completed orders, the assistant's version placed the condition in the HAVING clause, which resulted in an incorrect output. The correct approach would be to filter the rows first using the WHERE clause and then test the totals using HAVING.
This distinction is crucial because WHERE determines which rows are allowed into the groups, while HAVING decides which finished groups are included in the result.
Lastly, the fourth check requires naming the denominator when calculating the average order value for completed orders. Failing to specify the correct denominator can lead to inaccurate averages. Ensuring the proper denominator is used is essential for obtaining reliable results.
In summary, by following these five checks, AI-generated SQL queries can be thoroughly examined to ensure the accuracy of the numbers they produce. These simple steps can save time and prevent costly errors caused by the inherent limitations of AI-generated code.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
