Porting NULL and empty-string semantics across Oracle, SQL Server, MySQL and SQLite
Port NULL handling across Oracle, SQL Server, MySQL and SQLite by fixing four portability points: how a NULL is tested, what a comparison against NULL returns, whether boolean contexts coerce NULL, and what an empty string means. Oracle treats a zero-length character as null but warns this may change, while SQL Server depends on ANSI_NULLS, MySQL returns NULL for comparisons and coerces 0 and NULL to false in boolean contexts, and SQLite's IS and IS NOT are an extension.
On this page
Key idea
Port NULL handling across Oracle, SQL Server, MySQL and SQLite by fixing four portability points: how a NULL is tested, what a comparison against NULL returns, whether boolean contexts coerce NULL, and what an empty string means. The highest-risk item is Oracle, which currently treats a character value of length zero as null, but Oracle documentation warns this may not remain true in future releases and recommends not treating empty strings the same as nulls. Use IS NULL and IS NOT NULL for testing, because any other condition involving NULL evaluates to UNKNOWN, and UNKNOWN behaves almost like FALSE in WHERE but NOT UNKNOWN stays UNKNOWN. SQL Server adds a configuration trade-off: with SET ANSI_NULLS ON, one or two NULL operands yield UNKNOWN, while with ANSI_NULLS OFF the equals and not-equals operators treat NULL as a known value equal to other NULLs and return only TRUE or FALSE, changing filtering results depending on session settings. MySQL keeps comparisons with NULL returning NULL, but its boolean context treats 0 or NULL as false and anything else as true, and it regards two NULLs as equal in GROUP BY while placing NULLs first on ASC and last on DESC ordering. SQLite offers compact IS and IS NOT operators that always return 1 or 0 and never NULL, but documents this as a SQLite extension, with standard SQL requiring IS NOT DISTINCT FROM and IS DISTINCT FROM instead. The trade-off is consistency versus portability: engine-specific forms are concise and readable locally, while IS NULL / IS NOT NULL plus explicit COALESCE-style handling is the only spelling that behaves predictably everywhere. Record these four points per engine as a written matrix rather than assuming standard SQL behavior carries over, and add PostgreSQL as a row you verify against its own documentation, since no PostgreSQL source is available here.
Context and recommendation
Before porting queries or shared SQL modules across Oracle, SQL Server, MySQL and SQLite, build an explicit portability checklist for NULL: (1) how a NULL is tested, (2) what a comparison against NULL returns, (3) whether boolean contexts coerce NULL, and (4) what an empty string means. The single highest-risk item is Oracle, which currently treats a character value of length zero as null, while explicitly recommending that you not rely on empty strings being the same as nulls. Record these four points per engine as a written matrix rather than assuming standard SQL behavior carries over, and add PostgreSQL as a row you verify against its own documentation, since no PostgreSQL source is available here.
The core portability rule is to test for NULL with IS NULL and IS NOT NULL only. Oracle states that IS NULL and IS NOT NULL are the only comparisons to use for nulls, that any other condition involving null evaluates to UNKNOWN, and that UNKNOWN behaves almost like FALSE in WHERE but NOT UNKNOWN stays UNKNOWN rather than becoming TRUE. SQL Server has a genuine configuration trade-off: with SET ANSI_NULLS ON, one or two NULL operands yield UNKNOWN, while with ANSI_NULLS OFF the equals and not-equals operators treat NULL as a known value equal to other NULLs and return only TRUE or FALSE, which changes filtering results depending on session settings. MySQL keeps comparisons with NULL returning NULL, but its boolean context treats 0 or NULL as false and anything else as true, and it regards two NULLs as equal in GROUP BY while placing NULLs first on ASC and last on DESC ordering. SQLite offers compact IS and IS NOT operators that always return 1 or 0 and never NULL, but documents this as a SQLite extension, with standard SQL requiring IS NOT DISTINCT FROM and IS DISTINCT FROM instead.
The trade-off is consistency versus portability: engine-specific forms are concise and readable locally, while IS NULL / IS NOT NULL plus explicit COALESCE-style handling is the only spelling that behaves predictably everywhere. Do not assume that a query written for one engine will run unchanged on another, and do not assume that an empty string and a NULL are interchangeable. The matrix should capture the four points per engine, plus the session-level settings that can change results, and it should be treated as a checklist for code review rather than a one-time audit.
Use the source excerpts as evidence for the matrix: Oracle documentation says to use only IS NULL and IS NOT NULL, that any other condition involving nulls evaluates to UNKNOWN, that a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN, and that Oracle currently treats a character value with a length of zero as null but recommends not treating empty strings the same as nulls. SQL Server documentation says that when SET ANSI_NULLS is ON, an operator that has one or two NULL expressions returns UNKNOWN, and when SET ANSI_NULLS is OFF, the equals and not-equals operators treat NULL as a known value, equivalent to any other NULL, and only return TRUE or FALSE. MySQL documentation shows that 1 = NULL, 1 <> NULL, 1 < NULL and 1 > NULL all return NULL, and that 0 IS NULL is 0 while '' IS NULL is 0 and '' IS NOT NULL is 1. SQLite documentation says that it is not possible for an IS or IS NOT expression to evaluate to NULL, that the compact forms are an extension, and that standard SQL requires IS NOT DISTINCT FROM and IS DISTINCT FROM instead.
-- portable across the engines covered here
SELECT
'' IS NULL AS empty_is_null,
'' IS NOT NULL AS empty_is_not_null,
1 = NULL AS eq_null,
1 <> NULL AS ne_null;
-- run the same probe twice on SQL Server: once with ANSI_NULLS ON, once OFF
SET ANSI_NULLS ON;
SELECT 1 = NULL;
SET ANSI_NULLS OFF;
SELECT 1 = NULL;
-- row filtering: only this form is portable
SELECT * FROM t WHERE col IS NOT NULL;Reasoning and trade-offs
The engines agree on the core rule but diverge in the edges that break portability. Oracle states that IS NULL and IS NOT NULL are the only comparisons to use for nulls, that any other condition involving null evaluates to UNKNOWN, and that UNKNOWN behaves almost like FALSE in WHERE but NOT UNKNOWN stays UNKNOWN rather than becoming TRUE. SQL Server has a genuine configuration trade-off: with SET ANSI_NULLS ON, one or two NULL operands yield UNKNOWN, while with ANSI_NULLS OFF the equals and not-equals operators treat NULL as a known value equal to other NULLs and return only TRUE or FALSE, which changes filtering results depending on session settings. MySQL keeps comparisons with NULL returning NULL, but its boolean context treats 0 or NULL as false and anything else as true, and it regards two NULLs as equal in GROUP BY while placing NULLs first on ASC and last on DESC ordering. SQLite offers compact IS and IS NOT operators that always return 1 or 0 and never NULL, but documents this as a SQLite extension, with standard SQL requiring IS NOT DISTINCT FROM and IS DISTINCT FROM instead.
The trade-off is consistency versus portability: engine-specific forms are concise and readable locally, while IS NULL / IS NOT NULL plus explicit COALESCE-style handling is the only spelling that behaves predictably everywhere. Engine-specific forms such as SQLite's IS and IS NOT are concise, but they are an extension and will not run unchanged on engines that only accept IS NOT DISTINCT FROM. Oracle's empty-string-as-null behavior is concise but version-sensitive, and Oracle documentation recommends not treating empty strings the same as nulls. SQL Server's ANSI_NULLS setting can silently change the meaning of = and <>, so a query that looks portable may return different rows depending on the session configuration. MySQL's boolean coercion of 0 and NULL to false applies in boolean contexts, not to = and <> comparisons, which still return NULL, so a query that works in a WHERE clause may fail in a CASE expression or a function argument.
The matrix should therefore record not only the literal results of probes but also the session settings that affect them. For SQL Server, read the actual ANSI_NULLS setting before running the probe, because the default may differ from the documented behavior in some configurations. For Oracle, record the version and retest after upgrades, because the empty-string-as-null rule is documented as possibly changing in future releases. For MySQL, distinguish boolean contexts from comparison operators, and remember that ORDER BY places NULLs first on ASC and last on DESC. For SQLite, remember that IS and IS NOT are extensions, and that standard SQL requires IS NOT DISTINCT FROM and IS DISTINCT FROM instead. The matrix should also record whether the engine supports IS NOT DISTINCT FROM, because that is the portable spelling for equality of NULLs in standard SQL.
The recommendation is to write queries that use IS NULL and IS NOT NULL for testing, and to use explicit COALESCE or equivalent handling for values that may be NULL. This is the only spelling that behaves predictably everywhere, because it does not depend on the engine's treatment of empty strings, the session setting, or the boolean context. When you need to distinguish an empty string from a NULL, store them in separate columns or use a sentinel value, and do not rely on the engine's implicit conversion. The matrix should be used as a checklist for code review, and it should be updated whenever a new engine version changes the documented behavior.
Concrete illustration
Run the same small probe on every target engine and record the literal output as your portability evidence: SELECT '' IS NULL, '' IS NOT NULL, 1 = NULL, 1 <> NULL, 0 = NULL; plus a boolean-context probe such as SELECT NOT NULL in engines that accept it in a boolean position. On Oracle you should see the empty string behave as null, while Oracle documentation warns this may not remain true in future releases; MySQL documents '' IS NULL as 0 and '' IS NOT NULL as 1, and 1 = NULL, 1 <> NULL returning NULL; SQLite returns 1 or 0 from IS and IS NOT rather than NULL. Follow with a row filter probe, WHERE value <> NULL and WHERE value IS NOT NULL, to see which rows each engine actually returns. Keep the probe values in a fixture table so differences in stored empty strings versus nulls show up explicitly rather than being inferred.
The fixture table should contain one NULL, one empty string, one zero and one normal value, and it should be created in an isolated copy so that the probe does not affect production data. On Oracle, the empty string is stored as NULL, so the probe should show '' IS NULL as 1 and '' IS NOT NULL as 0, while 1 = NULL and 1 <> NULL should return NULL. On MySQL, the probe should show '' IS NULL as 0 and '' IS NOT NULL as 1, while 1 = NULL, 1 <> NULL, 1 < NULL and 1 > NULL should all return NULL. On SQLite, the probe should show '' IS NULL as 0 and '' IS NOT NULL as 1, while 1 = NULL and 1 <> NULL should return NULL, and IS and IS NOT should always return 1 or 0. On SQL Server, the probe should be run twice, once with SET ANSI_NULLS ON and once with SET ANSI_NULLS OFF, because the equals and not-equals operators return UNKNOWN when ANSI_NULLS is ON and TRUE or FALSE when ANSI_NULLS is OFF.
The row filter probe should show which rows each engine actually returns. WHERE value <> NULL should return no rows on Oracle, MySQL and SQLite, because the comparison returns NULL and a condition evaluating to UNKNOWN acts almost like FALSE in WHERE. WHERE value IS NOT NULL should return all rows that are not NULL, and it is the only portable form. On SQL Server, WHERE value <> NULL may return rows if ANSI_NULLS is OFF, because the operator treats NULL as a known value equal to other NULLs, so the probe must be run with the actual session setting. The boolean-context probe should show that NOT NULL evaluates to UNKNOWN in engines that follow three-valued logic, and that a WHERE clause with UNKNOWN returns no rows, while a boolean context that coerces UNKNOWN to FALSE may behave differently.
Record the literal output of each probe in the matrix, and include the session setting for SQL Server. The matrix should also record whether the engine supports IS NOT DISTINCT FROM, and whether the empty-string-as-null rule is documented as current behavior that may change. The probe results are evidence for the portability checklist, and they should be used to decide which spelling to use in shared SQL modules. The portable spelling is IS NULL and IS NOT NULL plus explicit COALESCE-style handling, because it does not depend on the engine's treatment of empty strings, the session setting, or the boolean context.
Applicability limits
This tip covers NULL comparison and empty-string semantics only; it does not cover aggregate counting of nulls, index behavior, or performance. The sources do not include PostgreSQL documentation, so no PostgreSQL NULL or empty-string behavior is asserted here and it must be checked against PostgreSQL's own docs before you extend the matrix. Oracle's zero-length-character rule is documented as current behavior that may change, so treat it as a version-sensitive assumption to retest on upgrades rather than a permanent guarantee. SQL Server results depend on the ANSI_NULLS setting, so probe the actual session configuration instead of assuming defaults. MySQL's boolean coercion of 0 and NULL to false applies in boolean contexts, not to = and <> comparisons, which still return NULL. SQLite's compact IS and IS NOT forms are an extension, so a query using them will not run unchanged on engines that only accept IS NOT DISTINCT FROM.
The limits are important because the matrix is a checklist, not a guarantee. Oracle's empty-string-as-null rule is documented as possibly changing in future releases, so a query that works today may break after an upgrade. SQL Server's ANSI_NULLS setting can change the meaning of = and <>, so a query that looks portable may return different rows depending on the session configuration. MySQL's boolean coercion of 0 and NULL to false applies in boolean contexts, not to = and <> comparisons, which still return NULL, so a query that works in a WHERE clause may fail in a CASE expression or a function argument. SQLite's compact IS and IS NOT forms are an extension, so a query using them will not run unchanged on engines that only accept IS NOT DISTINCT FROM. The matrix should therefore be used as a checklist for code review, and it should be updated whenever a new engine version changes the documented behavior.
The matrix should also record whether the engine supports IS NOT DISTINCT FROM, because that is the portable spelling for equality of NULLs in standard SQL. If an engine does not support it, the portable spelling is IS NULL and IS NOT NULL plus explicit COALESCE-style handling. The matrix should not be used to infer behavior from one engine to another, and it should not be used to assume that an empty string and a NULL are interchangeable. The probe results are evidence for the portability checklist, and they should be used to decide which spelling to use in shared SQL modules. The portable spelling is IS NULL and IS NOT NULL plus explicit COALESCE-style handling, because it does not depend on the engine's treatment of empty strings, the session setting, or the boolean context.
The sources do not include PostgreSQL documentation, so no PostgreSQL NULL or empty-string behavior is asserted here and it must be checked against PostgreSQL's own docs before you extend the matrix. The matrix should be treated as a checklist for code review, and it should be updated whenever a new engine version changes the documented behavior. The probe results are evidence for the portability checklist, and they should be used to decide which spelling to use in shared SQL modules. The portable spelling is IS NULL and IS NOT NULL plus explicit COALESCE-style handling, because it does not depend on the engine's treatment of empty strings, the session setting, or the boolean context.
Applicability
- Does the target engine treat '' IS NULL as 0 and '' IS NOT NULL as 1, or does it collapse the empty string to NULL?
- Does a comparison such as 1 = NULL return NULL, UNKNOWN, or TRUE/FALSE depending on the session setting?
- Does a boolean context such as WHERE NOT NULL or IF(NOT NULL) coerce NULL to FALSE, or does it preserve UNKNOWN?
- Does the engine's ORDER BY place NULLs first on ASC and last on DESC, or does it follow a different rule?
- Does GROUP BY treat two NULLs as equal, and does the engine support IS NOT DISTINCT FROM?
- Is the empty-string-as-null rule documented as current behavior that may change in future releases?
- Is the compact IS and IS NOT syntax documented as an extension rather than standard SQL?
- Are the results affected by a session-level setting such as ANSI_NULLS, and can you read that setting before running the probe?
- Does the probe fixture distinguish a stored NULL from a stored empty string, or are the two conflated?
- Have you verified the matrix against PostgreSQL documentation before extending it to that engine?
Where this applies
This tip covers NULL comparison and empty-string semantics only; it does not cover aggregate counting of nulls, index behavior, or performance. The sources do not include PostgreSQL documentation, so no PostgreSQL NULL or empty-string behavior is asserted here and it must be checked against PostgreSQL's own docs before you extend the matrix. Oracle's zero-length-character rule is documented as current behavior that may change, so treat it as a version-sensitive assumption to retest on upgrades rather than a permanent guarantee. SQL Server results depend on the ANSI_NULLS setting, so probe the actual session configuration instead of assuming defaults. MySQL's boolean coercion of 0 and NULL to false applies in boolean contexts, not to = and <> comparisons, which still return NULL. SQLite's compact IS and IS NOT forms are an extension, so a query using them will not run unchanged on engines that only accept IS NOT DISTINCT FROM.