PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteNULL means a value is missing, unknown, or otherwise unavailable; 0 is a real numeric value; and '' is text with zero characters in databases that preserve empty strings separately. They are not interchangeable. The important exception is Oracle Database 18c, which currently treats a zero-length character value as NULL.
What NULL, an empty string, and zero mean
| Value | Meaning | Example |
|---|---|---|
NULL |
No value is recorded, or the value is unknown or not applicable. It is not a normal value that can be compared with =. |
A contact’s phone number has not been provided. |
'' |
A character string containing zero characters. It is a text value, not a number, in databases that distinguish it from NULL. |
A text field is known to contain no characters. |
0 |
A numeric value whose amount is zero. | A measured balance or item count is actually zero. |
These distinctions affect both what a row means and how a query finds it. For example, MySQL’s documentation illustrates NULL as a phone number that is not known and an empty string as a person known to have no phone. That is a useful modeling example, not a meaning every application must assign to those values. MySQL: Working with NULL Values
How the major databases treat empty strings
| Database documentation | Empty string versus NULL | NULL comparison guidance |
|---|---|---|
| MySQL 26.7 | Distinct; the manual demonstrates separate inserts and filters for NULL and ''. |
Use IS NULL; = NULL does not find NULL rows in the manual’s example. Source |
| Oracle Database 18c | A character value with length zero is currently treated as NULL. Oracle warns this may change and advises applications not to treat the two as interchangeable. Source |
Use IS NULL or IS NOT NULL. |
| SQL Server, documentation labeled SQL Server 17 | NULL differs from an empty value and from zero. Source | Use IS NULL or IS NOT NULL. |
| PostgreSQL 17 | Empty text is distinct from NULL. |
Use IS NULL; for null-aware equality, PostgreSQL provides IS NOT DISTINCT FROM. Source |
Do not assume that Oracle’s behavior applies to PostgreSQL or other databases. Conversely, code that relies on an empty string remaining distinct from NULL should be checked against the actual database engine and version.
How to test for NULL and empty text
Use IS NULL and IS NOT NULL to test whether a value is null. For an empty string, use equality with '' only on a database that stores it distinctly.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
-- Find rows where the phone value is NULL
SELECT * FROM contacts WHERE phone IS NULL;
-- Find rows containing a zero-length string where the database distinguishes it
SELECT * FROM contacts WHERE phone = '';
-- This is not a NULL test and does not find NULL rows
SELECT * FROM contacts WHERE phone = NULL;The last condition does not evaluate to true for a null value. Ordinary comparisons involving NULL produce UNKNOWN rather than TRUE or FALSE, so a WHERE clause using phone = NULL filters those rows out. Oracle’s treatment of empty strings also means phone = '' cannot be relied on to identify a separate empty-string category there.
Why comparisons with NULL behave differently
SQL conditions use three-valued logic: TRUE, FALSE, and UNKNOWN. A comparison cannot determine whether an unknown value is equal to a given value, so the result is UNKNOWN. In a WHERE filter, UNKNOWN is not accepted as true; it is therefore easy to write a query that silently omits null rows. Microsoft documents the distinction between NULL and empty or zero values, while PostgreSQL documents the three-valued logical behavior. Microsoft Learn: NULL and UNKNOWN · PostgreSQL: Logical Operators
When you need equality that treats two nulls as matching, PostgreSQL supports IS NOT DISTINCT FROM. It returns true if both operands are NULL and otherwise behaves like equality for non-null operands. Confirm the equivalent operator or syntax before using this pattern in another database.
Which value should you store?
- Use
NULLwhen the value is unknown, missing, or not meaningful under your data model. - Use
''when the value is known to be text with zero characters and the target database preserves that distinction. - Use numeric
0when zero is the actual measured or counted value.
Decide what each state means for the application before choosing a column representation. For instance, “phone number not yet known” and “person has no phone” may be different states; storing both as NULL loses that distinction unless another field records it. Database defaults, constraints, and engine-specific settings can also affect how inserted values are stored, so validate the behavior for the column and database you use. MySQL documents conditional special handling for some column types and settings, including certain TIMESTAMP cases. MySQL: Problems with NULL Values
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.
