When Correct SQL Returns the Wrong Answer

ByJu Yeon Eum

A retention team asks for the list of customers who have never ordered. The query runs in a second and returns nothing. That is plausible — a mature business might have no such customers — so the empty list goes into the weekly review, and the campaign it was meant to feed is cancelled. The query had no error. It also had a wrong answer, caused by one guest order stored without a customer ID.

SQL did exactly what it defines in that case. The problem was the distance between what the statement means and what its author read it as meaning. This article works through the places where that distance is largest: joins that decide which rows exist, NULL that turns two-valued questions into three-valued ones, order that exists only where it was requested, and aggregation performed at a level the question did not ask about. It is about meaning rather than speed; how engines execute queries efficiently is a separate subject. The examples are invented and small, and they were run on DuckDB 1.5.5 and SQLite 3.53.4, with the relevant PostgreSQL 18 and MySQL 8.4 documentation checked in September 2026. Where engines disagree, that is stated.

Why wrong queries rarely fail

A database raises errors for things it can detect: invalid syntax, unknown columns, type mismatches it cannot resolve, constraint violations. It has no way to detect that a result answers a different question from the one intended, because the intended question exists only in someone’s head or a ticket. Three properties then keep the error hidden.

  • The output is well formed. The right columns, the right types, a reasonable number of rows.
  • The error is usually small or plausible. A few percent off, an empty list that could be true, a ranking with the right members in a slightly wrong order.
  • There is rarely an independent figure to compare against. The query is often the only way the number is produced.

So the defense cannot be reading results and noticing they look odd. It has to be knowing where the semantics diverge from intuition, and testing those places deliberately.

The examples below use two tiny tables. Three customers, one with no region recorded; four orders, one of them a guest order with no customer ID, and two placed on the same date.

customers                    orders
customer_id | region         order_id | customer_id | order_date | amount | status
C1          | EU             1        | C1          | 2026-03-01 | 100    | paid
C2          | US             2        | C1          | 2026-03-01 | 50     | cancelled
C3          | NULL           3        | C2          | 2026-03-02 | 80     | paid
                             4        | NULL        | 2026-03-02 | 30     | paid

Joins decide which rows exist

A join is usually read as “bring in columns from another table.” It also decides which rows survive and how many times each appears, and those effects are where most silent errors start. The best-known one is fan-out: joining an order to its several items repeats the order amount once per item, which is join cardinality producing double counting. Three less-discussed effects deserve the same attention.

A filter that quietly turns an outer join into an inner one

The request is “number of paid orders per customer, including customers with none.” A left join is the right start, and the obvious place for the status filter is WHERE.

-- Returns C1 = 1, C2 = 1. C3 has disappeared.
SELECT c.customer_id, COUNT(o.order_id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.customer_id;

-- Returns C1 = 1, C2 = 1, C3 = 0.
SELECT c.customer_id, COUNT(o.order_id) AS paid_orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id;

The PostgreSQL documentation states the rule precisely: a restriction in the ON clause is processed before the join, while a restriction in WHERE is processed after it, which “does not matter with inner joins, but it matters a lot with outer joins.” In the first query, C3 is kept by the left join with a NULL status, and then removed by a WHERE condition that NULL cannot satisfy. The result is an inner join wearing the syntax of an outer one, and the customers the request specifically asked about are exactly the ones missing.

Rows whose join key is NULL match nothing

Equality with NULL is never true, so the guest order cannot join to any customer. Total revenue from orders alone is 260. Total revenue after an inner join to customers is 230, and nothing reports the missing 30. The same row behaves differently in a GROUP BY customer_id, where it forms its own group with a NULL key. So a report that sums orders by customer and a report that sums orders by customer region can disagree, each internally consistent, because one grouped the NULL rows and the other joined them away.

Lookup tables that are not unique on the key you join by

Joins to “reference” tables — exchange rates by date, a product’s category, a region’s manager — are written on the assumption that the lookup side has one row per key. When it does not, because two rates were loaded for one date or a category was recorded twice, every matching fact row is duplicated. No step fails. The assumption was never checked, which is the whole problem: grain is a property to be tested on the actual data, not declared once in a design document.

NULL: a third answer where two were expected

A comparison with NULL evaluates to unknown, and WHERE keeps only rows for which the condition is true. The basic rules are covered in SQL NULL and three-valued logic. What causes damage in practice is how those rules combine.

Filters and their complements do not cover everything

region = 'EU' finds C1. region <> 'EU' finds C2. C3 is in neither. Any report built as “EU versus the rest” silently omits every row with an unknown region, and the two halves do not add up to the total. The honest version names the third group explicitly, with IS NULL, and decides whether it belongs in “the rest” or is reported separately.

NOT IN and the empty result

This is the opening example.

-- Returns no rows, because one order has customer_id = NULL.
SELECT customer_id
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

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

PostgreSQL’s documentation describes why: if there are no equal right-hand values and at least one right-hand row yields null, the result of NOT IN is “null, not true.” C3 is not equal to C1 or C2, but whether it equals NULL is unknown, so the whole condition is unknown and the row is dropped. One NULL anywhere in the subquery empties the result for every customer. NOT EXISTS asks a different question — does any matching row exist — and is unaffected. The pattern of keeping rows that have no match on the other side is an anti-join, and writing it with NOT EXISTS, or with a left join and a test for a missing key, is a sensible default rather than a stylistic preference.

NULL is equal to NULL in some places and not in others

The same two NULLs are treated differently depending on which part of SQL is looking at them, and that inconsistency is a reliable source of disagreement between queries that look equivalent.

ContextAre two NULLs treated as the same?Consequence
= in WHERE or JOINNo — the result is unknownNULL keys never match; rows drop out of inner joins
DISTINCT and GROUP BYYes — PostgreSQL documents this for DISTINCT, MySQL for bothAll NULLs collapse into one group or one distinct value
UNIQUE constraintBy default not, in PostgreSQL — configurable with NULLS NOT DISTINCTA “unique” key column can hold several rows with NULL
IS NOT DISTINCT FROMYes, explicitlyA null-safe comparison when NULL should match NULL

The UNIQUE row is the one that surprises data teams. PostgreSQL’s documentation notes that, by default, a unique constraint does not treat two NULLs as equal, so duplicate rows containing a NULL in a constrained column can be stored, and that the SQL standard leaves this choice implementation-defined, so other databases behave differently. A declared unique key is therefore not proof that the column holds one row per value. Equality joins are unaffected, since NULL keys match nothing, but anything that groups, deduplicates, or compares null-safely on that column will find several rows sharing the NULL.

Constraints have a related gap. A CHECK constraint is satisfied if its expression evaluates to true or to null, so CHECK (amount > 0) does not reject a NULL amount. Only a not-null constraint does.

Aggregates change their population

COUNT(*) counts rows; COUNT(amount) counts non-NULL amounts; AVG(amount) divides by the second, not the first. An average over a column that is NULL for unrecorded values is an average over recorded values only, which may or may not be the question. At the edge, PostgreSQL’s documentation points out that, except for count, aggregate functions return null when no rows are selected — “sum of no rows returns null, not zero.” Wrapping the result in COALESCE(..., 0) is common and often right, but it is a claim that no data means zero, not a formatting step. The broader aggregation rules are in SQL aggregation.

Order exists only where you asked for it

Tables have no inherent order, and neither do query results unless a sort is requested. PostgreSQL’s documentation puts it directly: without a sort, rows are returned in an unspecified order that depends on the scan and join plan and the order on disk, and “must not be relied on.” The consequences go well beyond display.

LIMIT and “top N” without a unique order

“Top ten customers by revenue” is well defined only if no two customers at the boundary have the same revenue. The documentation for LIMIT warns that without an ORDER BY that constrains the result into a unique order, the query returns “an unpredictable subset” of rows, and that different LIMIT and OFFSET values can produce different plans and inconsistent results. Paginated exports are the classic casualty: a row can appear on two pages or on none. The remedy is deterministic ordering — sorting by the business order and then by a column that is unique, so that ties are broken the same way every time.

Deduplication that picks a different winner each run

Keeping the latest status per order is usually written with ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) and a filter on row number 1. If an order has two status updates with the same timestamp — say “paid” and “refunded” logged in the same second — the two rows are tied, and PostgreSQL’s window function tutorial states that tied rows are numbered in an unspecified order. The query picks one. It may pick the other on the next run, on another engine, or after the table is rewritten, and the order flips between paid and refunded without any code change.

A tie-breaker only fixes this if it reflects the order in which the changes happened at the source. A version or sequence number the source assigns to each change of that entity qualifies. A load sequence number does not: it records the order rows arrived, and arrival order is not event order. A “paid” row that was delayed and loaded after the “refunded” row gets the higher load number and flips the status back — the same distinction Apache Beam draws between the time an event occurs and the time it is processed, noting that data “isn’t always guaranteed to arrive in a pipeline in time order.” A source log position works too, but only within one log: positions taken from different databases, or from before and after a re-seed, are not comparable, and joining feeds without checking that is how a reversal gets reintroduced. The status name in alphabetical order qualifies as nothing at all. If the data holds no column that decides which update came last, the honest outcome is to surface the tie as a data problem rather than resolve it arbitrarily.

Window frames include peers by default

A running total is written as SUM(amount) OVER (ORDER BY order_date). With two orders on each date, the result is not what most people expect.

order_id | order_date | amount | SUM OVER (ORDER BY order_date) | with ROWS frame, ordered by date then order_id
1        | 2026-03-01 | 100    | 150                            | 100
2        | 2026-03-01 | 50     | 150                            | 150
3        | 2026-03-02 | 80     | 260                            | 230
4        | 2026-03-02 | 30     | 260                            | 260

The explanation is in the default frame. PostgreSQL’s documentation defines it as RANGE UNBOUNDED PRECEDING, which with an ORDER BY covers every row from the start of the partition through the current row’s last peer — rows that the window’s ORDER BY cannot tell apart. Both orders on 1 March are peers, so both see a total that includes the other. Neither column in the table is wrong; they answer different questions, “total up to and including this date” and “total up to this row.” The error is using one while believing it is the other.

The same default makes LAST_VALUE misleading. It returns the last row of the frame, and with the default frame that is the current row’s last peer rather than the last row of the partition; the documentation itself calls this likely to give “unhelpful results.” For the true last value, the frame has to be stated, for example ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. The general rule, covered in more detail in window functions and frames, is to write the frame explicitly whenever the result depends on it.

Aggregating at a level the question did not ask about

The last family of errors comes from computing a correct number at one level and then combining it as if it were valid at another.

  • Averages of averages. Suppose, in a separate invented example, one region has 2 orders averaging 100 and another has 98 orders averaging 50. The mean of the two regional averages is 75. The average order value across all 100 orders is 51. Averaging stored averages weights each group equally regardless of size. Store or recompute from sums and counts instead.
  • Distinct counts do not add. A customer active on five days is counted once in each day’s distinct count and once in the month’s. Summing daily active customers therefore overstates monthly active customers by every repeat visit. A distinct count has to be computed at the level being reported, from the underlying identifiers.
  • Filters applied at the wrong level. Filtering rows before grouping and filtering groups after grouping answer different questions, and a threshold on an order-level value is not the same as the same threshold on a customer’s total. Which one a request means has to be established, not inferred from the SQL someone wrote first.
  • Arithmetic that depends on types. A conversion rate computed as paid_orders / total_orders over integer columns is, in PostgreSQL, integer division, which the documentation states truncates toward zero — so 3 paid orders out of 4 gives 0. The same expression is not portable: in the test for this article, 1/2 returned 0 on SQLite and 0.5 on DuckDB. Casting to a decimal type before dividing makes the intent explicit, and type conversion has its own traps to check.

Engines disagree on the details

Several of the behaviors above are not fixed by SQL itself, which means the same query can return different results on different engines without any error. The ones checked for this article:

BehaviorWhat variesWhere it was checked
Where NULLs sort by defaultPostgreSQL sorts NULLs as larger than any value, so last in ascending order; MySQL puts them first in ascending order. In testing, DuckDB placed them last and SQLite firstPostgreSQL 18 and MySQL 8.4 documentation; DuckDB 1.5.5 and SQLite 3.53.4 runs
Integer divisionPostgreSQL truncates toward zero; SQLite returned 0 for 1/2; DuckDB returned 0.5PostgreSQL 18 documentation; SQLite and DuckDB runs
NULLs under a unique constraintThe SQL standard leaves it implementation-defined; PostgreSQL allows several NULLs by default and lets the table choosePostgreSQL 18 documentation

The practical consequence is for migrations and multi-engine platforms. Moving a query from one engine to another is a change in meaning until proven otherwise, and a “top N” report, a ratio, or a uniqueness assumption is exactly where it shows. Anything not listed here should be checked in the documentation of the engine actually in use rather than assumed.

Catching errors that never raise

Because these errors produce plausible output, reviewing the output is a weak control. Testing the conditions that make them possible is a strong one, and most of the tests are cheap.

RiskTest that detects it
Fan-out from a lookup joinAssert that the lookup side is unique on the join key, including whether the key allows NULL; compare row counts before and after the join
Rows lost by joins or filtersReconcile a total — revenue, order count — before and after each join, and account for the difference
NULL in keys and filter columnsTrack the NULL rate of join keys and of columns used in WHERE, and alert when it moves
NOT IN over a nullable columnA review or lint rule that flags it; prefer NOT EXISTS
Non-deterministic deduplication or top NCount ties on the ordering columns; zero ties should be an assertion, not an assumption
Frame-dependent window resultsRequire an explicit frame in any window aggregate or LAST_VALUE
Everything above, togetherA small fixture dataset that deliberately contains NULL keys, ties, empty groups, and unmatched rows, with the expected answers written down

The last row is the most valuable, because it encodes the meaning of a query rather than its syntax. A fixture with a guest order and a customer with no region would have caught the opening example in the first test run.

Tests settle whether a query does what its author intended. They cannot settle whether that intention is the right one. Whether a customer with no region belongs in “the rest,” whether no orders means zero revenue or no data, whether a tie means “refunded” or “unknown” — those are definitional decisions, and they belong with whoever owns the metric definition. Recording them there, next to the metric, is what stops the next query author from making the opposite choice silently. Where a figure matters enough, an independent reconciliation against a source outside the query is still the only check that does not share the query’s assumptions.

Questions that reveal the wrong choice

QuestionIf the answer is unclear
Is every filter on an outer-joined table in ON rather than WHERE, unless removing unmatched rows is intended?The outer join is an inner join, and unmatched rows vanish
Is each lookup table unique on the key you join by, and can that key be NULL?Facts are duplicated, or unmatched, with no error
Does any NOT IN read from a column that can be NULL?One NULL empties the whole result
Where a filter splits rows into groups, where do NULLs go?The groups do not add up to the total
Does every LIMIT, top-N, and deduplication order by something unique?Different runs return different rows
Does every window aggregate state its frame?Running totals jump on ties and LAST_VALUE returns the wrong row
Are ratios, averages, and distinct counts computed at the level being reported?Group sizes are ignored or repeat visitors are counted twice
Would this query return the same result on another engine you use?A migration changes numbers without any failure

References

PostgreSQL 18 and MySQL 8.4 documentation was checked on September 16, 2026. The examples were run on DuckDB 1.5.5 and SQLite 3.53.4. Default behaviors can change between versions, so confirm them for the engine and version you use.

Discover more from Insightful Data Lab

Subscribe to get the latest posts sent to your email.

Similar Posts

Questions, corrections, or additional insights?

This site uses Akismet to reduce spam. Learn how your comment data is processed.