Free tools Windows power users keep installed
One-click scans. No signup required.
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A SQL query can execute successfully and still produce a plausible but incorrect result. The cause is often a mismatch between the query’s apparent intent and its actual semantics: NULL values can defeat an anti-match, a WHERE condition can discard rows a LEFT JOIN preserved, a one-to-many join can inflate a sum, a window frame can turn a total into a running total, or an inclusive timestamp boundary can omit most of the final day.
The examples below follow PostgreSQL behavior. Check your database engine and version before relying on defaults, especially for window frames and timestamp handling.
1. Why does NOT IN return no rows when the subquery has a NULL?
This anti-match looks for customers whose IDs do not appear in the orders table:
SELECT c.id
FROM customers AS c
WHERE c.id NOT IN (SELECT o.customer_id FROM orders AS o);If the subquery returns even one NULL, a comparison that finds no matching non-NULL value can evaluate to NULL (unknown), not TRUE. A WHERE clause keeps only rows for which its condition is TRUE, so the query may return no rows even when some customers have no matching order. PostgreSQL’s NOT IN guidance illustrates this behavior.
#1 Best Overall
Use an absence test that handles NULL deliberately
NOT EXISTS is often clearer for checking that no matching row exists:
SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
);The equality comparison does not match a NULL order key, so such a row does not block the customer from being returned. Decide separately what a NULL c.id should mean; with ordinary equality, it will not match another NULL. If the intended rule is to ignore missing order keys in the anti-match, explicitly filter them from the subquery. Do not assume that the two forms have identical NULL behavior.
2. Why did my LEFT JOIN turn into an inner join?
A LEFT JOIN retains every row from its left input and supplies NULLs for right-side columns when there is no match. But a later WHERE condition can discard those null-extended rows:
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →SELECT a.id, b.status
FROM accounts AS a
LEFT JOIN events AS b ON b.account_id = a.id
WHERE b.status = 'open';For an account without an event, b.status is NULL and b.status = 'open' is not TRUE. WHERE removes that account. PostgreSQL’s table-expression documentation describes join conditions and inputs; its SELECT documentation explains WHERE filtering.
Choose WHERE or ON according to the intended result
If you want every account and only want to attach open events when they exist, 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 you want only accounts with an open event, filtering in WHERE is appropriate. To validate a preservation requirement, test a known account with no matching event and confirm it remains in the result.
3. Why is my SUM too high after joining two tables?
Consider summing order totals after joining each order to its items:
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;An order with three matching items appears in three joined rows, so its total is added three times. The sum is correct for the joined rows, but those rows are at item grain, while order_total is an order-level value. PostgreSQL’s table-expression reference describes the rows formed by joins, and its GROUP BY reference explains how those input rows are grouped before aggregation.
Match the aggregation to the intended grain
- If you need customer totals from orders, aggregate orders before joining item details.
- If you need measures from both orders and items, aggregate each fact table to the intended reporting grain separately, then join those aggregates.
- If the second table is needed only to test whether a match exists, use
EXISTSrather than a join that multiplies rows.
Compare row counts and distinct order IDs before and after each join. Avoid using SUM(DISTINCT o.order_total) as a general repair: two separate orders can legitimately have the same total, and the distinct sum would collapse them.
4. Why does SUM() OVER (ORDER BY …) give me a running total?
This query appears to calculate one total, but in PostgreSQL the ordered aggregate window uses a default frame that runs through the current row’s last peer:
SELECT employee_id, salary,
SUM(salary) OVER (ORDER BY salary) AS total_salary
FROM employees;The result is a running total, and rows tied on salary share the same peer endpoint. PostgreSQL’s window-function tutorial contrasts the ordered version with SUM(salary) OVER (). It also notes that tied rows in row_number ordering are numbered in an unspecified order unless the sort breaks the tie.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Specify whether you want a total or a running calculation
- Whole result: use
SUM(salary) OVER (). - Department total on each detail row: use
SUM(salary) OVER (PARTITION BY department_id). - Running sum: define the ordering and frame explicitly, for example
SUM(salary) OVER (ORDER BY salary, employee_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Include a unique tie-breaker when row-by-row order matters.
A window function operates on the virtual table produced after FROM, WHERE, GROUP BY, and HAVING filtering, as the PostgreSQL tutorial explains. Rows removed earlier cannot contribute to the window calculation.
5. Why does BETWEEN miss rows on the end date?
BETWEEN includes both endpoints. With a timestamp column, this condition can stop at midnight at the beginning of October 7:
WHERE created_at BETWEEN '2026-10-01' AND '2026-10-07'Later times on October 7 are beyond that upper bound. PostgreSQL’s date and time guidance demonstrates the boundary problem.
Use a half-open time interval
Include the starting instant and exclude the start of the next period:
WHERE created_at >= '2026-10-01'
AND created_at < '2026-10-08'For a month or another period, calculate the next period’s start rather than inventing an end-of-day timestamp. When timestamps represent absolute instants, compute the boundary in the intended business time zone and use an appropriate timezone-aware type. Timestamp and time-zone rules differ across database engines, so confirm the target engine’s behavior.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Two more quiet aggregate surprises
SUM over no rows may be NULL, not zero
In PostgreSQL, sum over no selected rows returns NULL; count is the exception among built-in aggregates. Use COALESCE(SUM(amount), 0) only when the application’s meaning of “no observations” is genuinely zero. Otherwise, replacing NULL can erase the distinction between no data and a measured zero. See PostgreSQL’s aggregate-function reference.
Order-dependent aggregates need their own ordering
PostgreSQL does not guarantee input order for aggregates such as array_agg and string_agg unless order is specified. Put the ordering inside the aggregate call, such as string_agg(name, ', ' ORDER BY name), when the sequence is part of the required result. The same aggregate reference documents this behavior.
A quick way to diagnose a plausible but wrong result
- Check whether nullable keys enter a
NOT INsubquery. - Test a known unmatched left-side row after a LEFT JOIN and any right-side filters.
- Compare row counts and distinct keys before and after joins; state the intended grain before summing.
- Inspect window ordering, peer ties, and frame boundaries.
- Check whether time filters use an exclusive next-period boundary in the intended time zone.
- Distinguish an aggregate over no rows from a true numeric zero.
These examples describe PostgreSQL semantics. Confirm the corresponding NULL rules, window defaults, and date-time behavior in the engine and version that runs your query.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Quick Recap
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.
