NULL Handling

SELECT NULL = NULL;   -- Result: NULL (not TRUE!)
SELECT NULL <> NULL;  -- Result: NULL
SELECT NULL = 0;      -- Result: NULL
SELECT NULL = '';     -- Result: NULL
SELECT NULL IS NULL;  -- Result: TRUE ✅ (use IS NULL!)

NULL = NULL → UNKNOWN (not TRUE!)
NULL > 5   → UNKNOWN
NULL + 5   → NULL

-- ✅ Correct: Use IS NULL / IS NOT NULL
SELECT * FROM employees WHERE phone IS NULL;
SELECT * FROM employees WHERE phone IS NOT NULL;

-- ❌ Wrong: Can't use = NULL or != NULL
SELECT * FROM employees WHERE phone = NULL;   -- returns no rows!
SELECT * FROM employees WHERE phone != NULL;  -- returns no rows!

-- Find employees who DON'T have a manager
SELECT name, manager_id
FROM employees
WHERE manager_id IS NULL;  -- ✅ correct

-- ❌ This returns nothing!
SELECT name, manager_id
FROM employees
WHERE manager_id = NULL;

-- Sample data: salaries = [90000, 80000, NULL, 75000, NULL]

SELECT COUNT(*)      FROM employees;  -- 5  (counts ALL rows)
SELECT COUNT(salary) FROM employees;  -- 3  (ignores NULLs)
SELECT AVG(salary)   FROM employees;  -- 81666 (90000+80000+75000)/3, NOT /5
SELECT SUM(salary)   FROM employees;  -- 245000
SELECT MAX(salary)   FROM employees;  -- 90000
SELECT MIN(salary)   FROM employees;  -- 75000 (ignores NULL)

-- If all values are NULL:
SELECT AVG(salary) FROM employees WHERE 1=0;  -- Returns NULL!
SELECT SUM(salary) FROM employees WHERE 1=0;  -- Returns NULL!
SELECT COUNT(*)    FROM employees WHERE 1=0;  -- Returns 0  (only COUNT(*) returns 0)

-- INNER JOIN excludes rows with NULL on the join key
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- ❌ Employees with NULL dept_id are EXCLUDED

flowchart LR
    subgraph Employees[employees table]
        E1[Alice | dept_id: 1]
        E2[Bob | dept_id: 2]
        E3[Carol | dept_id: NULL]
    end

    subgraph Departments[departments table]
        D1[1: Engineering]
        D2[2: Marketing]
    end

    E1 -->|JOIN ON dept_id| D1
    E2 -->|JOIN ON dept_id| D2
    E3 -.->|NULL can't match| X[❌ Excluded from INNER JOIN]

    style E1 fill:#3b82f6,color:#fff
    style E2 fill:#3b82f6,color:#fff
    style E3 fill:#ef4444,color:#fff
    style X fill:#ef4444,color:#fff

-- ❌ Dangerous: NOT IN with NULL in subquery returns NOTHING!
SELECT name FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments);
-- If any dept_id in departments is NULL, the whole query returns 0 rows!

-- Why? WHERE dept_id NOT IN (1, 2, NULL)
--     → dept_id != 1 AND dept_id != 2 AND dept_id != NULL
--     → dept_id != NULL is UNKNOWN (not TRUE)
--     → So the whole WHERE is UNKNOWN for every row!

-- ✅ Safe alternative: use NOT EXISTS
SELECT name FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.dept_id = e.dept_id
);

-- COALESCE: replace NULL with a default
SELECT name, COALESCE(salary, 0) AS salary
FROM employees;
-- If salary is NULL, show 0 instead

SELECT name, COALESCE(phone, 'No phone') AS phone
FROM employees;
-- If phone is NULL, show 'No phone'

-- IFNULL (MySQL-specific)
SELECT name, IFNULL(salary, 0) AS salary FROM employees;

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(0, 0);    -- NULL
SELECT NULLIF(5, 0);    -- 5

-- Safe division
SELECT name,
    revenue / NULLIF(employees, 0) AS revenue_per_emp
FROM departments;

-- NULLs sort last by default in MySQL (ASC)
SELECT name, manager_id
FROM employees
ORDER BY manager_id ASC;
-- NULLs come at the end

-- Control NULL position
SELECT name, manager_id
FROM employees
ORDER BY manager_id IS NULL, manager_id;
-- NULLs first (IS NULL = 1, IS NOT NULL = 0)