The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →A SQL query can execute successfully and still give a plausible but incorrect result. The cause is often a mismatch between what the query appears to say and how SQL handles NULLs, joins, aggregation, window frames, or timestamp boundaries. These examples follow PostgreSQL behavior; confirm relevant defaults and type handling in your database and version.
Why does NOT IN return no rows when the subquery has a NULL?
NOT IN looks like a direct way to find records with no match. But if its subquery returns a NULL, a comparison that does not match a non-NULL value can evaluate to unknown—not true. A WHERE clause keeps only rows for which its condition is true, so the anti-match may unexpectedly return no rows. PostgreSQL’s documentation wiki explains this NULL behavior.
As an Amazon Associate I earn from qualifying purchases.
SELECT c.id
FROM customers AS c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders AS o);Use NOT EXISTS for an absence test, and decide explicitly whether an outer row with a NULL key should count as unmatched:
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);With this equality predicate, a NULL customer ID does not match an order ID; NOT EXISTS will therefore include that customer. If that is not the intended business rule, add an explicit condition to handle outer NULL keys. Alternatively, exclude NULLs from the subquery when that matches the rule, but do not assume that filtering only the subquery settles how outer NULLs should be treated.
#1 Best Overall
What to check
- Check whether the subquery key can contain NULL, for example with
WHERE customer_id IS NULL. - Test the anti-match with a known matching key, a known non-matching key, and a NULL key on each side where applicable.
Why did my LEFT JOIN turn into an inner join?
A LEFT JOIN retains unmatched rows from its left input by filling right-side columns with NULL. A later WHERE condition on one of those right-side columns can remove those rows: the condition is not true for NULL. The result then contains only rows with a qualifying match, as PostgreSQL’s table-expression documentation describes for join inputs and conditions.
SELECT a.id, b.status
FROM accounts AS a
LEFT JOIN events AS b ON b.account_id = a.id
WHERE b.status = 'open';If the goal is to keep every account and attach only its open events, put the status condition in the join condition:
SELECT a.id, b.status
FROM accounts AS a
LEFT JOIN events AS b
ON b.account_id = a.id
AND b.status = 'open';If the goal is to return only accounts with an open event, the original filtering approach expresses that requirement. PostgreSQL distinguishes row filtering with WHERE from group filtering with HAVING in its SELECT reference. For a complex query, verify that a known account without events survives when preservation is required.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsWhy is my SUM too high after joining two tables?
Aggregates operate on the rows produced by the joins. If one order has several matching items, its order total appears once per item in the joined rows; summing at customer level adds those repeated amounts. The query is summing the joined row set correctly, but that set has item-level rather than order-level grain. PostgreSQL documents how joins form input rows and how GROUP BY groups them before aggregation in its table-expression reference.
SELECT o.customer_id, SUM(o.order_total)
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
GROUP BY o.customer_id;Choose a repair based on the intended result:
- Need totals at order grain: aggregate orders before joining item details, or aggregate the order and item data separately before combining results.
- Need orders that have at least one item, but no item columns: use
EXISTSas a match test instead of joining item rows into the aggregate input. - Need a customer total: establish the intended grain first, then sum one order-level value per order.
Compare row counts and distinct order IDs before and after each join to find where multiplicity changes. Avoid using SUM(DISTINCT o.order_total) as a general fix: two different orders may legitimately have the same total, and the distinct sum would collapse them.
Why does SUM() OVER (ORDER BY ...) give me a running total?
In PostgreSQL, an aggregate window with ORDER BY uses a default frame that runs from the partition start through the current row’s last peer. That produces a running aggregate; rows tied on the ordering value share the peer endpoint. The PostgreSQL 18 window-functions tutorial demonstrates the difference between an unordered whole-window sum and an ordered one. It also notes that tied rows in row_number are numbered in an unspecified order unless the ordering breaks the tie.
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY salary) AS total_salary
FROM employees;Choose the window definition that matches the question:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware match- Whole result total on every row:
SUM(salary) OVER (). - Department total on every employee row:
SUM(salary) OVER (PARTITION BY department_id). - Running total row by row: specify a stable ordering and an explicit frame, such as
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Add a unique tie-breaker if tied salaries must have a defined row-by-row sequence.
Window functions use the virtual table formed after the query’s FROM, WHERE, GROUP BY, and HAVING processing, as the PostgreSQL 18 tutorial explains. A window total therefore reflects the rows that remain at that stage, not necessarily every row in the underlying table.
Why does BETWEEN miss rows on the end date?
BETWEEN includes both endpoints. In PostgreSQL, a date-like upper bound used with a timestamp can represent midnight at the start of that date, leaving later timestamps on that same date outside the range. The PostgreSQL wiki’s timestamp guidance illustrates the boundary problem.
WHERE created_at BETWEEN '2026-10-01' AND '2026-10-07'For a period covering October 1 through October 7, use a half-open interval: inclusive at the start and exclusive at the next period boundary.
WHERE created_at >= '2026-10-01'
AND created_at < '2026-10-08'When the values represent absolute instants, calculate those boundaries in the intended business time zone and use an appropriate timezone-aware timestamp type. Timestamp and time-zone behavior can vary by engine and type, so confirm the interpretation in the target database rather than treating this PostgreSQL guidance as universal.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Two other silent aggregate surprises in PostgreSQL
Does SUM return zero when no rows match?
No. PostgreSQL returns NULL for sum over no selected rows; count is an exception among built-in aggregates. Use COALESCE(SUM(amount), 0) only when the application’s meaning of “no rows” is genuinely zero rather than unknown or absent. See the PostgreSQL aggregate-function documentation.
Is the order of array_agg or string_agg guaranteed?
Not by default in PostgreSQL. If the order of elements or concatenated values is part of the result, put an ORDER BY inside the aggregate call, as described in the aggregate documentation. An outer query’s ordering does not define the order in which values were fed to the aggregate.
Quick Recap
A quick way to diagnose plausible but wrong results
- Unexpectedly empty anti-match: inspect NULLs in both compared keys and verify the intended treatment of outer NULLs.
- Missing left-side records: test a known unmatched row and inspect right-side predicates in
WHERE. - Inflated totals: compare row counts and distinct keys before and after joins; state the grain each aggregate should represent.
- Unexpected window values: check partitioning, ordering, frame, and tie-breakers.
- Missing boundary timestamps: inspect the actual start and end instants, timestamp types, and time zone used to compute the interval.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
