Audit intermediate Works with: Any AI

SQL NULL Handling and Hidden Data Loss Audit

Checklist Has example

• 🗄️ SQL

Review a SQL query for NULL traps in joins, filters, comparisons and aggregates that silently drop or distort rows.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Review the following query for mistakes caused by NULL values. Database: [DATABASE_TYPE]. Query: [QUERY]. Table columns with notes on which allow NULL: [COLUMN_NOTES]. What the report is meant to show: [REPORT_PURPOSE].

Examine every WHERE, JOIN, CASE, GROUP BY and aggregate. Produce: 1) A table with the columns Line or clause, Column involved, NULL risk (comparison that is never true, NOT IN with a NULL, outer join filtered back to inner, average ignoring NULL, concatenation returning NULL, sort order), Effect on results, and Safer rewrite. 2) A short plain-language explanation of three-valued logic for a beginner, in under 100 words. 3) A set of check queries that count rows where each relevant column is NULL, so I can see how many rows are affected. 4) A final rewritten query with comments, keeping the intended meaning unchanged. 5) Two business questions the report owner must answer, such as whether missing values should count as zero or be excluded.

Rules: do not decide business meaning for me; ask. Do not invent column semantics. Behaviour of functions such as COALESCE, NULL ordering and string concatenation differs between databases, so mark those details [VERIFY] for [DATABASE_TYPE]. Recommend running the checks on a read-only copy before changing the production report.

Add a worked example with invented figures: if 1,000 orders include 80 with a NULL discount and the query averages the discount, show how the average differs when NULL is excluded versus treated as zero.

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Invented data: 1,000 orders; 920 have a discount, 80 have NULL. The 920 discounts add up to 4,600.

Average ignoring NULL (default AVG behaviour): 4,600 / 920 = 5.00
Average treating NULL as zero: 4,600 / 1,000 = 4.60

Difference: 0.40, or 8% lower.

Check query to run first:
SELECT COUNT(*) AS total_rows, COUNT(discount) AS non_null_rows, COUNT(*) - COUNT(discount) AS null_rows FROM orders;

Question for the report owner: does "no discount recorded" mean zero discount or unknown? The answer decides which figure belongs in the report.