NULL handling (COALESCE/NVL/NULLIF/IS NULL)

Oracle

-- COALESCE: returns the first non-NULL value (standard)
SELECT name, COALESCE(phone, mobile, 'none') AS contact FROM customer;

-- Oracle-specific NVL / NVL2
SELECT NVL(balance, 0) FROM customer;
SELECT NVL2(discount, 'yes', 'no') FROM orders;

-- NULLIF and IS NULL
SELECT NULLIF(a, b) FROM t;   -- NULL if a=b
SELECT name FROM customer WHERE phone IS NULL;

For handling NULL (unknown value): COALESCE returns the first non-NULL value in the list (standard, multiple arguments). Oracle's NVL(a,b) is the two-argument equivalent; NVL2 returns different values depending on whether the value is NULL or not. NULLIF(a,b) returns NULL if they are equal. Always test for NULL with IS NULL / IS NOT NULL (= NULL does not work).

PostgreSQL

SELECT name, COALESCE(phone, mobile, 'none') AS contact FROM customer;
SELECT NULLIF(divisor, 0) FROM t;   -- prevent division by zero
SELECT name FROM customer WHERE phone IS NULL;
-- The equivalent of Oracle NVL is COALESCE

PostgreSQL offers standard COALESCE, NULLIF and IS NULL/IS NOT NULL; use COALESCE instead of Oracle's NVL. Use IS, not =, for NULL comparisons; if two NULLs must be treated as equal, the 'IS NOT DISTINCT FROM' operator does the job.

MySQL

SELECT name, COALESCE(phone, mobile, 'none') AS contact FROM customer;

-- MySQL shortcut: IFNULL (two arguments)
SELECT IFNULL(balance, 0) FROM customer;
SELECT NULLIF(a, b) FROM t;
SELECT name FROM customer WHERE phone IS NULL;

MySQL/MariaDB support standard COALESCE and NULLIF; IFNULL(a,b) is a two-argument shortcut (similar to Oracle NVL). The <=> operator provides NULL-safe equality (treats two NULLs as equal). NULL is tested with IS NULL / IS NOT NULL.

SQL Server

SELECT name, COALESCE(phone, mobile, 'none') AS contact FROM customer;

-- SQL Server shortcut: ISNULL (two arguments)
SELECT ISNULL(balance, 0) FROM customer;
SELECT NULLIF(a, b) FROM t;
SELECT name FROM customer WHERE phone IS NULL;

SQL Server offers standard COALESCE (multiple arguments, with data type promotion) and the two-argument ISNULL(a,b); the two differ in subtle ways (COALESCE is standard and more flexible with type precedence). NULLIF and IS NULL are also supported. ISNULL is the equivalent of Oracle NVL (the names are similar).

MongoDB

// COALESCE equivalent: $ifNull
db.customer.aggregate([
  { $project: { name: 1,
      contact: { $ifNull: ["$phone", "$mobile", "none"] } } }
])
// NULL / missing field check:
db.customer.find({ phone: { $in: [null] } })

In MongoDB aggregation the COALESCE equivalent is the $ifNull operator (multiple arguments, returns the first that is not null/missing). In a query, { field: null } matches both documents whose value is null and documents that lack the field entirely; make the distinction explicit with { field: { $type: 'null' } } or $exists.

Redis

# Instead of NULL, Redis has the concept of 'no such key/field':
EXISTS customer:1              # does the key exist?
HGET customer:1 phone        # returns nil if the field is absent
GET missing_key                  # (nil)

Redis has no NULL in the SQL sense; the equivalent is a missing key or a missing Hash field, and commands return nil in that case. Default-value logic (like COALESCE) is applied on the application side with a nil check. Field existence is checked with HEXISTS.

Elasticsearch

# Default assignment when a field is missing (missing bucket / null_value)
# Find documents without the field in a query:
GET customer/_search
{ "query": { "bool": { "must_not": { "exists": { "field": "phone" } } } } }
# Map empty values to a specific value with null_value in the mapping

In Elasticsearch null or missing fields are checked with the 'exists' query (must_not exists = documents without the field). For a COALESCE-like default, define 'null_value' in the mapping, or assign a value to missing entries with the 'missing' parameter in aggregations. Defaults can also be produced with runtime fields/scripts.