NULL represents unknown/missing data. Incorrect NULL handling causes bugs in queries, reports, and applications. Critical for data integrity.

E-Commerce Optional Fields

Handling optional customer data with NULL:

CUSTOMERS (

customer_id PK,

email VARCHAR(100) NOT NULL,

phone VARCHAR(20) NULL,

middle_name VARCHAR(50) NULL,

company VARCHAR(100) NULL

)

NULL = optional field not provided

NOT NULL = required field

NULL allows optional fields without forcing empty strings. COALESCE provides fallback for display. COUNT(phone) counts only customers who provided phone number.

Find customers with missing phone

SELECT customer_id, email FROM customers WHERE phone IS NULL;

Display contact with fallback

SELECT customer_id, COALESCE(phone, email, 'No contact') AS contact FROM customers;

Count customers with phone

SELECT COUNT(*) AS total_customers, COUNT(phone) AS customers_with_phone FROM customers;

All

Employee Bonus Calculations

NULL-safe salary calculations:

EMPLOYEES (

employee_id PK,

name VARCHAR(100) NOT NULL,

salary DECIMAL(10,2) NOT NULL,

bonus DECIMAL(10,2) NULL,

commission DECIMAL(10,2) NULL

)

NULL bonus = no bonus earned

NULL commission = not commission-based role

COALESCE prevents NULL propagation (salary + NULL = NULL). AVG(bonus) calculates average only for employees with bonus. IS NOT NULL filters employees who earned bonus.

Calculate total compensation (NULL-safe)

SELECT name, salary + COALESCE(bonus, 0) + COALESCE(commission, 0) AS total_comp FROM employees;

Find employees with bonus

SELECT name, salary, bonus FROM employees WHERE bonus IS NOT NULL ORDER BY bonus DESC;

Average bonus (excludes NULLs)

SELECT AVG(bonus) AS avg_bonus, COUNT(bonus) AS employees_with_bonus FROM employees;

All

Order Tracking with Optional Dates