25 — NULL Handling

NULL = NULL    -- UNKNOWN
NULL <> NULL   -- UNKNOWN
NULL = 1       -- UNKNOWN
NULL > 1       -- UNKNOWN
NULL IS NULL   -- TRUE  (special syntax)
NULL IS NOT NULL -- FALSE

-- These NEVER return rows:
WHERE col = NULL        -- always UNKNOWN
WHERE col != NULL       -- always UNKNOWN
WHERE col <> NULL       -- always UNKNOWN

-- These work correctly:
WHERE col IS NULL
WHERE col IS NOT NULL

-- Watch out with NOT:
WHERE NOT (col = 1)     -- excludes NULLs (evaluates to UNKNOWN, row dropped)
WHERE col <> 1          -- excludes NULLs for the same reason
-- To include NULLs in the "not 1" case:
WHERE col <> 1 OR col IS NULL

-- Returns rows where @Filter is NULL (no filter applied) OR col matches
WHERE (@Filter IS NULL OR col = @Filter)

-- Setup
CREATE TABLE #A (id INT, val INT NULL);
CREATE TABLE #B (id INT, val INT NULL);
INSERT #A VALUES (1, 10), (2, NULL), (3, 20);
INSERT #B VALUES (1, 10), (2, NULL), (4, 30);

-- INNER JOIN: row with val=NULL does NOT join to row with val=NULL
SELECT a.id, b.id
FROM #A a
JOIN #B b ON a.val = b.val;
-- Returns: (1,1), (3, nothing) → actually only (1,1)
-- NULL=NULL is UNKNOWN → excluded

-- To join on NULL-matching columns, use:
SELECT a.id, b.id
FROM #A a
JOIN #B b ON a.val = b.val
          OR (a.val IS NULL AND b.val IS NULL);

SELECT a.id,
       b.id            AS b_id,
       b.val           AS b_val,
       CASE WHEN b.id IS NULL THEN 'no match' ELSE 'matched' END AS status
FROM #A a
LEFT JOIN #B b ON a.id = b.id;

CREATE TABLE #Sales (amount DECIMAL(10,2) NULL);
INSERT #Sales VALUES (100), (200), (NULL), (NULL), (300);

SELECT
    COUNT(*)        AS all_rows,         -- 5  (includes NULLs)
    COUNT(amount)   AS non_null_rows,    -- 3  (ignores NULLs)
    SUM(amount)     AS total,            -- 600 (ignores NULLs)
    AVG(amount)     AS average,          -- 200 = 600/3 (denominator excludes NULLs!)
    MIN(amount)     AS minimum,          -- 100
    MAX(amount)     AS maximum           -- 300
FROM #Sales;

-- If you want AVG over all rows (treating NULL as 0):
SELECT AVG(ISNULL(amount, 0)) AS avg_incl_null_as_zero  -- 120 = 600/5
FROM #Sales;

SELECT 1 + NULL         -- NULL
SELECT 'hello' + NULL   -- NULL (with ANSI_NULLS ON)
SELECT NULL / 0         -- NULL (not an error)
SELECT ABS(NULL)        -- NULL
SELECT UPPER(NULL)      -- NULL

-- Simple CASE: NULL does NOT match any value
SELECT CASE NULL
    WHEN NULL THEN 'null'   -- never matches
    ELSE 'other'            -- always goes here
END;   -- returns 'other'

-- Searched CASE: use IS NULL explicitly
SELECT CASE
    WHEN col IS NULL THEN 'null'
    WHEN col = 1    THEN 'one'
    ELSE 'other'
END
FROM t;

-- NULL in the IN list does NOT cause the NULL row to match:
SELECT * FROM t WHERE col IN (1, NULL, 3);
-- Equivalent to: col=1 OR col=NULL OR col=3
-- col=NULL is UNKNOWN, so NULLs in col are excluded

ISNULL(check_expression, replacement_value)

SELECT ISNULL(NULL, 'default')           -- 'default'
SELECT ISNULL(CAST(NULL AS INT), 42)     -- 42 (INT)
SELECT ISNULL(CAST(NULL AS VARCHAR(5)), 'toolong')  -- 'toolon' (truncated to 5!)

COALESCE(expression1, expression2, ..., expressionN)

SELECT COALESCE(NULL, NULL, 'third')     -- 'third'
SELECT COALESCE(col1, col2, 'fallback') FROM t;

-- Type precedence example:
SELECT COALESCE(NULL, 42)               -- 42 (INT — higher precedence than NULL)
SELECT COALESCE(CAST(NULL AS FLOAT), 42)  -- 42.0 (FLOAT wins over INT)

-- COALESCE can evaluate the subquery twice (expanded to CASE):
SELECT COALESCE((SELECT MAX(id) FROM t WHERE active = 1), 0)
-- May run the subquery once for the IS NULL check, once for the value

-- Safer: use ISNULL or assign to variable first:
DECLARE @val INT = (SELECT MAX(id) FROM t WHERE active = 1);
SELECT ISNULL(@val, 0);

NULLIF(expression1, expression2)

SELECT NULLIF(col, '')    -- returns NULL where col = ''
SELECT NULLIF(col, 0)     -- returns NULL where col = 0
SELECT NULLIF(col, col)   -- always NULL (trivial, don't do this)

-- Avoid divide-by-zero:
SELECT numerator / NULLIF(denominator, 0) FROM t;
-- Returns NULL instead of divide-by-zero error when denominator = 0

-- Treat both '' and NULL as missing, then provide default:
SELECT ISNULL(NULLIF(email, ''), 'unknown@example.com') FROM users;

-- Pre-2022: null-safe equality required verbose pattern
WHERE (col = @val OR (col IS NULL AND @val IS NULL))

-- 2022+: null-safe equality
WHERE col IS NOT DISTINCT FROM @val

-- Pre-2022: null-safe inequality
WHERE NOT (col = @val OR (col IS NULL AND @val IS NULL))
-- ...or equivalently:
WHERE (col <> @val OR (col IS NULL) <> (@val IS NULL))

-- 2022+: null-safe inequality
WHERE col IS DISTINCT FROM @val

SELECT a.id, b.id
FROM #A a
JOIN #B b ON a.val IS NOT DISTINCT FROM b.val;
-- NULL in a.val matches NULL in b.val

CREATE TABLE #u (id INT IDENTITY, val INT NULL, UNIQUE (val));
INSERT #u (val) VALUES (1);   -- OK
INSERT #u (val) VALUES (1);   -- ERROR: duplicate key value 1
INSERT #u (val) VALUES (NULL); -- OK
INSERT #u (val) VALUES (NULL); -- OK! Second NULL is allowed

CREATE TABLE #m (a INT NULL, b INT NULL, UNIQUE (a, b));
INSERT #m VALUES (1, 1);     -- OK
INSERT #m VALUES (1, 1);     -- ERROR: duplicate (1,1)
INSERT #m VALUES (1, NULL);  -- OK
INSERT #m VALUES (1, NULL);  -- OK! Allowed because b is NULL
INSERT #m VALUES (NULL, NULL); -- OK
INSERT #m VALUES (NULL, NULL); -- OK! Both NULL — always allowed

-- See Section 10 for the filtered index approach

-- Allow multiple NULLs but require uniqueness among non-null values
CREATE UNIQUE INDEX UX_orders_tracking_notnull
ON orders (tracking_number)
WHERE tracking_number IS NOT NULL;
-- Now: two NULLs → OK; two identical tracking numbers → ERROR

-- deleted_at IS NULL means "active"
CREATE UNIQUE INDEX UX_users_email_active
ON users (email)
WHERE deleted_at IS NULL;
-- Allows multiple deleted rows with same email, but only one active row

-- Finding orphaned rows (FK column is NULL) is a table scan without an index
-- A filtered index makes it an index seek:
CREATE INDEX IX_orders_customerid_null
ON orders (order_id)
WHERE customer_id IS NULL;

-- Now this query uses the filtered index:
SELECT order_id FROM orders WHERE customer_id IS NULL;

-- Values: NULL, NULL, 1, 3, 5
SELECT val FROM t ORDER BY val ASC;
-- Result: NULL, NULL, 1, 3, 5

SELECT val FROM t ORDER BY val DESC;
-- Result: 5, 3, 1, NULL, NULL

-- SQL Server has no NULLS FIRST / NULLS LAST syntax (unlike PostgreSQL/Oracle)
-- Workaround: sort NULLs last in ascending order:
SELECT val
FROM t
ORDER BY CASE WHEN val IS NULL THEN 1 ELSE 0 END, val ASC;
-- Puts NULLs last regardless of ASC/DESC on val

CREATE TABLE #g (category VARCHAR(20) NULL, amount INT);
INSERT #g VALUES ('A', 10), ('A', 20), (NULL, 30), (NULL, 40), ('B', 50);

SELECT category, SUM(amount) AS total
FROM #g
GROUP BY category;
-- Result:
-- A      30
-- B      50
-- NULL   70   ← NULLs grouped together

SELECT category, amount,
       SUM(amount) OVER (PARTITION BY category) AS cat_total
FROM #g;
-- Rows where category IS NULL get their own partition (total = 70)

CREATE TABLE #chk (age INT NULL, CHECK (age >= 0));
INSERT #chk VALUES (25);    -- OK: TRUE
INSERT #chk VALUES (-1);    -- ERROR: FALSE
INSERT #chk VALUES (NULL);  -- OK! UNKNOWN → constraint passes

-- If you want to also prohibit NULLs, add NOT NULL:
ALTER TABLE #chk ALTER COLUMN age INT NOT NULL;

CREATE TABLE #range (
    start_date DATE NULL,
    end_date   DATE NULL,
    CHECK (end_date >= start_date)  -- passes if either is NULL
);
INSERT #range VALUES ('2024-01-01', '2023-01-01');  -- ERROR: FALSE
INSERT #range VALUES (NULL, '2023-01-01');           -- OK: UNKNOWN
INSERT #range VALUES ('2024-01-01', NULL);           -- OK: UNKNOWN

CHECK (end_date IS NULL OR end_date >= start_date)
-- Now: end_date=NULL passes, but start_date=NULL with a non-null end_date
-- still passes unless you add more conditions

CREATE TABLE #outer (id INT);
CREATE TABLE #inner (id INT NULL);
INSERT #outer VALUES (1), (2), (3);
INSERT #inner VALUES (1), (NULL);  -- note the NULL

-- NOT IN with NULL in subquery → returns ZERO rows
SELECT * FROM #outer WHERE id NOT IN (SELECT id FROM #inner);
-- Returns nothing! Because:
-- id NOT IN (1, NULL) → id<>1 AND id<>NULL → id<>1 AND UNKNOWN → UNKNOWN for all rows

-- Fix option 1: filter NULLs from subquery
SELECT * FROM #outer WHERE id NOT IN (SELECT id FROM #inner WHERE id IS NOT NULL);

-- Fix option 2: use NOT EXISTS (preferred — never affected by NULLs)
SELECT o.* FROM #outer o WHERE NOT EXISTS (SELECT 1 FROM #inner i WHERE i.id = o.id);

-- Fix option 3: LEFT JOIN anti-join
SELECT o.* FROM #outer o LEFT JOIN #inner i ON o.id = i.id WHERE i.id IS NULL;

SELECT 1 AS x UNION ALL SELECT NULL AS x
EXCEPT
SELECT NULL AS x;
-- Returns only: 1   (NULL is correctly excluded)

SELECT 'Hello ' + NULL + 'World'   -- NULL

-- Use COALESCE or ISNULL to guard:
SELECT 'Hello ' + ISNULL(middle_name, '') + ' World'  FROM contacts;

-- Or use CONCAT() which ignores NULLs (SQL Server 2012+):
SELECT CONCAT('Hello ', middle_name, ' World') FROM contacts;
-- Returns 'Hello  World' if middle_name IS NULL (treats NULL as empty string)

-- CONCAT_WS also ignores NULL arguments (but not the separator itself):
SELECT CONCAT_WS(', ', 'First', NULL, 'Last')  -- 'First, Last' (NULL skipped)
SELECT CONCAT_WS(NULL, 'First', 'Last')         -- NULL (NULL separator propagates)

SET ANSI_NULLS ON;   -- default
DECLARE @x INT = NULL;
SELECT CASE WHEN @x = NULL  THEN 'equal' ELSE 'not equal' END;  -- 'not equal' (UNKNOWN → ELSE)
SELECT CASE WHEN @x IS NULL THEN 'null'  ELSE 'not null'  END;  -- 'null'

SET ANSI_NULLS OFF;  -- non-standard legacy mode
SELECT CASE WHEN @x = NULL THEN 'equal' ELSE 'not equal' END;   -- 'equal' (!!)

-- Replace NULL with domain default at output time
SELECT
    customer_name,
    ISNULL(phone, 'N/A')           AS phone,
    COALESCE(mobile, home, work, 'no phone') AS best_phone
FROM customers;

-- Count how many rows have each status, NULL status goes into "unknown"
SELECT
    ISNULL(status, 'unknown') AS status,
    COUNT(*)                   AS row_count
FROM orders
GROUP BY ISNULL(status, 'unknown');

-- Legacy systems often use '' or -1 or 0 as "missing" sentinel values
-- Convert to NULL for consistent handling:
UPDATE legacy_data
SET phone    = NULLIF(phone, ''),
    age      = NULLIF(age, -1),
    score    = NULLIF(score, 0)
WHERE phone = '' OR age = -1 OR score = 0;

-- MERGE with null-safe matching (pre-2022):
MERGE target AS t
USING source AS s
ON t.key_col = s.key_col
   AND (t.nullable_col = s.nullable_col
        OR (t.nullable_col IS NULL AND s.nullable_col IS NULL))
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (key_col, nullable_col, val) VALUES (s.key_col, s.nullable_col, s.val);

-- SQL Server 2022:
MERGE target AS t
USING source AS s
ON t.key_col = s.key_col
   AND t.nullable_col IS NOT DISTINCT FROM s.nullable_col
WHEN MATCHED THEN UPDATE SET t.val = s.val
WHEN NOT MATCHED THEN INSERT (key_col, nullable_col, val) VALUES (s.key_col, s.nullable_col, s.val);

-- Use the first non-null column across a priority chain
SELECT
    order_id,
    COALESCE(override_price, contract_price, list_price) AS final_price
FROM orders;

-- Mark low-confidence readings as NULL for downstream processing
UPDATE sensor_readings
SET temperature = NULLIF(temperature, -999.9)  -- -999.9 = bad sensor value
WHERE sensor_id = 42;

SELECT
    c.name                                  AS column_name,
    t.name                                  AS data_type,
    c.is_nullable,
    c.max_length,
    c.precision,
    c.scale
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.orders')
ORDER BY c.column_id;

SELECT
    o.name          AS module_name,
    o.type_desc,
    m.uses_ansi_nulls
FROM sys.sql_modules m
JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.uses_ansi_nulls = 0;

SELECT
    i.name      AS constraint_name,
    i.is_unique,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS columns,
    -- Check if any column in the UNIQUE index is nullable
    MAX(CASE WHEN col.is_nullable = 1 THEN 1 ELSE 0 END) AS has_nullable_column
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c        ON ic.object_id = c.object_id AND ic.column_id = c.column_id
JOIN sys.columns col      ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE i.is_unique = 1
  AND i.object_id = OBJECT_ID('dbo.orders')
GROUP BY i.name, i.is_unique;

SELECT
    i.name          AS index_name,
    o.name          AS table_name,
    i.filter_definition,
    i.is_unique
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE i.has_filter = 1
ORDER BY o.name, i.name;