NULL Handling

-- Correct: IS NULL / IS NOT NULL
SELECT id, full_name, manager_id
FROM employees
WHERE manager_id IS NULL;     -- finds top-level employees (no manager)

-- Wrong: = NULL never matches anything
SELECT id FROM employees WHERE manager_id = NULL;   -- returns 0 rows!

-- Three-valued logic demonstration
SELECT
    NULL = NULL        AS eq_null_null,     -- UNKNOWN → false in WHERE
    NULL IS NULL       AS is_null,          -- TRUE
    NULL != 5          AS neq_five,         -- UNKNOWN
    NOT NULL           AS not_null_val,     -- UNKNOWN
    TRUE AND NULL      AS true_and_null,    -- UNKNOWN
    FALSE AND NULL     AS false_and_null,   -- FALSE (short-circuit)
    TRUE OR NULL       AS true_or_null;     -- TRUE (short-circuit)

-- NULL in aggregate functions
SELECT
    COUNT(*)           AS total_rows,          -- counts ALL rows
    COUNT(commission)  AS rows_with_commission,-- ignores NULLs
    AVG(commission)    AS avg_commission,      -- ignores NULLs (not divides by total)
    SUM(commission)    AS total_commission     -- NULLs treated as 0
FROM employees;

-- COALESCE: return first non-NULL value (works in all databases)
SELECT
    id,
    COALESCE(phone, mobile, 'N/A')   AS contact_number,
    COALESCE(discount, 0)             AS discount_amount
FROM orders;

-- NULLIF: returns NULL if two values are equal (prevents divide-by-zero)
SELECT
    department_id,
    SUM(sales) / NULLIF(COUNT(*), 0)  AS avg_sales_per_head
FROM employees
GROUP BY department_id;
-- Without NULLIF: division by zero error if a dept has 0 employees

-- MySQL IFNULL (two-argument only; less flexible than COALESCE)
SELECT IFNULL(phone, 'N/A') FROM users;

-- Sorting NULLs intentionally
SELECT id, full_name, commission
FROM employees
ORDER BY COALESCE(commission, -1) DESC;   -- NULLs sort as -1 (go last)

-- JPA: @Column(nullable=true) maps to nullable FK; use Optional<T> return type
// Optional<String> phone in entity → COALESCE handled at Java layer with Optional.orElse("N/A")