SQL Dialects & Portability

NULL, Boolean, Collation, and Expression Differences

Expression portability fails at semantic edges: null propagation, empty strings, boolean storage, implicit coercion, uniqueness, and locale-aware comparison can change rows and order. State these rules in schema/query contracts and test with adversarial values under the deployed configuration.

SQL null uses three-valued logic, but extensions differ.
Concatenation is not uniform.
Oracle collapses zero-length character values to null.
Boolean domains vary.
Unique plus null is vendor-specific.
Collation is data behavior, not decoration.

High-risk semantic edges
Concern	PostgreSQL	MySQL	SQLite	SQL Server	Oracle
Empty string	distinct from null	distinct from null	distinct from null	distinct from null	currently treated as null for character values
Boolean	native boolean	BOOLEAN synonym for TINYINT(1)	0/1 convention	bit	native BOOLEAN in 26ai; version-gate
Concatenation	`\|\|`; null operand yields null	`CONCAT`; null argument yields null (`\|\|` mode-sensitive)	`\|\|`; null operand yields null	`+`; CONCAT_NULL_YIELDS_NULL is always ON from SQL Server 2017 onward	`\|\|`; empty string/null behavior is Oracle-specific
Null-safe equality	`IS NOT DISTINCT FROM`	`<=>`	`IS` / `IS NOT`	`IS [NOT] DISTINCT FROM` in SQL Server 2022+	explicit equality-or-both-null predicate
UNIQUE nullable key	multiple nulls by default; `NULLS NOT DISTINCT` available	multiple nulls generally permitted	distinct nulls permitted	single-column unique index normally permits one null	multiple nulls generally permitted; empty strings become null

Explicit null-safe email comparison

SELECT customer_id FROM customers WHERE email IS NOT DISTINCT FROM :email;

Portable explicit null ordering

SELECT order_id, shipped_at
FROM orders
ORDER BY CASE WHEN shipped_at IS NULL THEN 1 ELSE 0 END,
         shipped_at ASC,
         order_id ASC;
-- The unique order_id tiebreaker makes pagination deterministic.
-- Verify expression indexes/plans if this is performance critical.