A query can run successfully and still drop rows, blur the meaning of NULLs, or silently change an input value. The danger is not limited to syntax errors: SQL’s rules for NULL comparisons and joins, along with database configuration, can make a result look plausible while it differs from what the application intended.
1. Comparing NULL with = instead of testing for NULL
In SQL, NULL represents an unknown or missing value; it does not behave like an ordinary value that can be matched with equality. A predicate such as column = NULL can evaluate to UNKNOWN rather than TRUE, so a WHERE clause does not select the row as an ordinary equality check would. MySQL’s documentation explicitly notes that expr = NULL returns no rows, while Microsoft explains that SQL’s three-valued logic is a source of application errors.
As an Amazon Associate I earn from qualifying purchases.
To test whether a value is absent, write column IS NULL; to select values that are present, write column IS NOT NULL. These are nullness tests, not comparisons to a special value.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Check the result with rows that contain both NULL and non-NULL values. Compare the count returned by the nullness test with the expected count for the relevant business rule.
#1 Best Overall
Sources: Microsoft Learn: NULL and UNKNOWN (Transact-SQL); MySQL 8.0 Reference Manual: Problems with NULL Values.
2. Assuming an equality join will match NULL keys
An equality join does not treat two NULL join keys as equal in the documented SQL Server behavior. If a row on either side has a NULL key, a condition such as left_table.key = right_table.key will not pair those rows. With an inner join, the unmatched left row is absent from the result; with a left join, it remains, but the right-side columns are NULL.
Before joining, check how many keys are NULL on each side and compare row counts before and after the join. If the intended output must retain every left-side row, use a left outer join—but do not assume that makes two NULL keys match. If the business rule says missing keys should correspond, define that rule explicitly rather than relying on equality.
Source: Microsoft Learn: Joins (SQL Server).
3. Reading an outer-join NULL as proof that no row matched
After a left outer join, a NULL in a right-side column has two possible explanations: a matching right-side row exists but that column itself is NULL, or no right-side row matched and the join supplied NULLs for its columns. The displayed value alone cannot distinguish those cases.
Include a right-side key that is guaranteed not to be NULL, or another unambiguous match indicator, in the result. Then inspect that indicator when deciding whether a row matched. This distinction matters when counting matches, diagnosing missing records, or applying later filters to the joined data.
Source: Microsoft Learn: Joins (SQL Server).
4. Expecting bad input to fail instead of being converted
Whether suspicious input is rejected can depend on database configuration. The MySQL 8.0 manual documents a specific example: in non-strict mode, inserting the string 'abc' into an integer column can convert it to 0 and produce a warning. An application that checks only whether the statement completed may therefore accept a value different from the one supplied.
This is a MySQL non-strict-mode example, not a rule for every database engine or configuration. Check the active SQL mode, and inspect warnings after statements that may involve coercion. For critical data, validate input before insertion and confirm that the stored value matches the intended value.
Recommended Free Tools
Source: MySQL 8.0 Reference Manual: Server SQL Modes.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.5. Trusting date, string, or floating-point assumptions without testing edge cases
MySQL 8.0’s query-issues index identifies case sensitivity in string searches, DATE-column problems, missing matches, and floating-point values as separate troubleshooting categories. That establishes them as areas where queries can surprise users; it does not establish one universal failure mechanism or fix across engines, versions, collations, and data types.
For a query involving any of these, test the assumptions that determine whether a row matches:
- String searches: Test values that differ only by letter case and confirm the behavior under the target column’s collation.
- Date filters: Test values exactly at the start and end of the intended range, and make the boundary rule explicit.
- Missing matches: Test unmatched values as well as values expected to match, then inspect the join type and filter conditions.
- Floating-point comparisons: Test representative stored values and the comparison boundaries used by the application; do not assume that a displayed decimal is an exact representation.
These are validation checks, not engine-independent remedies. Consult documentation for the database engine, version, collation, and data type actually in use before changing a query.
Source: MySQL 8.0 Reference Manual: Query-Related Issues.
How to catch a query that succeeds but is wrong
Use small, deliberately awkward sample rows to test the rule the query is supposed to implement. Include NULL keys, unmatched join keys, duplicate keys where relevant, boundary dates, and inputs that may require conversion. Compare counts before and after joins, verify key uniqueness assumptions, and inspect warnings as well as errors. A successful execution confirms that the database accepted the statement; it does not by itself confirm that the result reflects the intended meaning.
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.
