What is NULL in SQL? How do you handle NULL values?

-- ❌ Wrong — NULL = NULL is always UNKNOWN (not TRUE)
SELECT * FROM employees WHERE manager_id = NULL;   -- returns 0 rows!

-- ✅ Correct
SELECT * FROM employees WHERE manager_id IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

SELECT 100 + NULL;   -- NULL
SELECT NULL * 5;     -- NULL
SELECT NULL / 2;     -- NULL

-- This is a trap — NULL salary causes row to be invisible in AVG
SELECT AVG(salary) FROM employees;  -- NULL rows are ignored!
SELECT SUM(salary) FROM employees;  -- NULL rows are ignored!
SELECT COUNT(*) FROM employees;     -- counts all rows INCLUDING NULLs
SELECT COUNT(salary) FROM employees; -- skips NULL salaries

-- Return bonus, or 0 if bonus is NULL
SELECT name, COALESCE(bonus, 0) AS bonus
FROM employees;

-- Try multiple fallbacks
SELECT name, COALESCE(phone, email, 'No contact info') AS contact
FROM employees;

-- Replace NULL department with 'Unassigned'
SELECT name, COALESCE(department, 'Unassigned') AS department
FROM employees;

-- MySQL / MariaDB
SELECT name, IFNULL(bonus, 0) AS bonus FROM employees;

-- Oracle
SELECT name, NVL(bonus, 0) AS bonus FROM employees;

-- SQL Server
SELECT name, ISNULL(bonus, 0) AS bonus FROM employees;

-- Avoid divide-by-zero: return NULL instead
SELECT total_sales / NULLIF(total_orders, 0) AS avg_order_value
FROM sales_summary;
-- If total_orders = 0 → NULLIF returns NULL → division returns NULL (safe)

-- Replace empty strings with NULL
SELECT NULLIF(phone, '') AS phone FROM users;

-- NULLs appear LAST by default in ascending order
SELECT name, salary FROM employees ORDER BY salary ASC;

-- Force NULLs first
SELECT name, salary FROM employees ORDER BY salary ASC NULLS FIRST;  -- PostgreSQL/Oracle

-- MySQL workaround
SELECT name, salary FROM employees ORDER BY salary IS NULL ASC, salary ASC;

-- NULL is treated as its own group
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department;
-- department=NULL gets its own row in results

-- Rows with NULL join key will NOT match any row
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
-- Employees with dept_id = NULL are excluded from INNER JOIN
-- Use LEFT JOIN to include them

-- Find records with missing required data
SELECT id, name FROM users
WHERE email IS NULL OR phone IS NULL;

-- Validate no NULL in mandatory columns after insert
SELECT COUNT(*) AS null_count
FROM orders
WHERE customer_id IS NULL OR product_id IS NULL OR quantity IS NULL;
-- Expected: 0

-- Fill NULLs in report
SELECT
    order_id,
    COALESCE(discount, 0)        AS discount,
    COALESCE(notes, 'None')      AS notes
FROM orders;