There is one class of SQL errors that never produces an error message: the query runs, the numbers come out neatly, and the numbers are wrong. Almost always, the cause is the same, which is NULL. Here are the rules, with behavior as documented by the official database engine, not from habit.
NULL is not an empty string, and is not equal to anything
A phone column containing NULL means the number is unknown. A phone column containing an empty string means the person is known to not have a number. Two different things, and both are valid to store.
The consequences are harsh: in SQL, NULL is never true when compared to any value, including another NULL. So WHERE telepon = NULL returns zero rows, always, without warning. The correct way is IS NULL or IS NOT NULL.
The derived rule: any expression containing NULL results in NULL, unless stated otherwise. 1 + NULL results in NULL. CONCAT('Invisible', NULL) also results in NULL, not the word "Invisible".
COUNT(*) and COUNT(column) answer different questions
Aggregate functions like COUNT(), MIN(), and SUM() ignore NULL. The exception is COUNT(*), which counts rows, not column values.
This means SELECT COUNT(*), COUNT(umur) FROM orang can return 1,000 and 840 from the same table. Both are correct; what is wrong is calling both "data count".
The most insidious effect is in the average. AVG divides the total by the number of non-NULL values, not by the number of rows. If there are 160 empty rows, your average is calculated from 840 rows, and usually the result is higher than you think is being reported.
NOT IN with one NULL will empty the result
This is the trap that most often removes rows. If the list on the right contains at least one NULL and there are no matching values, the result of the NOT IN construct is NULL, not TRUE. Rows that result in NULL do not pass the WHERE clause, so your report loses entire rows that should appear.
The reason lies in its definition: NOT IN is equivalent to <> ALL, and comparisons with NULL never produce FALSE, only "unknown". Two safe alternatives: use NOT EXISTS, or filter out NULL in the subquery with WHERE column IS NOT NULL.
DISTINCT, GROUP BY, and ORDER BY treat NULL as equal
This is where the rules turn around, and it can be confusing. For comparison, two NULLs are never equal. But for DISTINCT, GROUP BY, and ORDER BY, all NULLs are treated as equivalent. Thus, GROUP BY produces one group of NULL, not one group per empty row.
The ordering also has fixed rules: in ascending ORDER BY, NULL appears first; in descending ORDER BY, NULL appears last. Useful when checking data, misleading when taking the top ten.
The rules are not uniform across engines
Do not transfer habits from one engine to another without checking. SQLite, for example, still allows a PRIMARY KEY column to contain NULL due to historical negligence that has been deliberately maintained for compatibility, except in WITHOUT ROWID and STRICT tables. SQLite also allows aggregate queries to include columns not in GROUP BY, something that is rejected by almost all other engines. A query that is "correct" on your laptop can change meaning on a production server.
Two tools to check it
PostgreSQL provides IS DISTINCT FROM and IS NOT DISTINCT FROM, which treat NULL like a regular value: NULL IS DISTINCT FROM NULL results in false, not NULL. For a quick check, there are also num_nulls() and num_nonnulls().
A cheap and impactful habit: before reporting any numbers, first count how many NULLs per column that are included in the count. One additional query is usually enough to determine whether the discrepancies arise from the data or from language rules.
References
- MySQL 8.4 Reference Manual, B.3.4.3 Problems with NULL Values, https://dev.mysql.com/doc/refman/8.4/en/problems-with-null.html
- PostgreSQL 18 Documentation, 9.24 Subquery Expressions, https://www.postgresql.org/docs/current/functions-subquery.html
- PostgreSQL 18 Documentation, 9.2 Comparison Functions and Operators, https://www.postgresql.org/docs/current/functions-comparison.html
- SQLite, Quirks, Caveats, and Gotchas In SQLite, https://sqlite.org/quirks.html