NULL foreign keys are a common source of bugs and missing data in reports. An order with NULL customer_id won't appear in "orders with customer names" report using INNER JOIN - the order vanishes. A product with NULL category_id disappears from category reports. Employee records with NULL department_id are excluded from department headcount. Understanding NULL behavior prevents these silent data losses. Every data analyst has debugged a report where "some records are missing" - usually caused by NULL foreign keys and INNER JOIN.

Order Processing with Optional Guest Checkout

E-commerce site allows guest checkout (orders without customer account). Orders have NULL customer_id for guests.

E-commerce

SELECT

o.id,

o.order_date,

COALESCE(c.name, o.guest_name) AS customer_name,

COALESCE(c.email, o.guest_email) AS email,

o.total,

CASE

WHEN o.customer_id IS NULL THEN 'Guest'

ELSE 'Registered'

END AS customer_type

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.id

WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'

ORDER BY o.order_date DESC;

NULL customer_id is valid for guest orders. LEFT JOIN includes all orders. COALESCE uses guest info when customer_id is NULL. This pattern handles optional relationships correctly. Marketing team can segment by customer_type to target guest conversion.

All

Employee Hierarchy with CEO

CEO has NULL manager_id (no manager). Organizational chart must include CEO.

HR Management

SELECT

e.id,

e.name,

e.title,

e.department,

COALESCE(m.name, 'No Manager') AS manager,

CASE

WHEN e.manager_id IS NULL AND e.title = 'CEO' THEN 'Top Level'

WHEN e.manager_id IS NULL THEN 'Data Issue'

ELSE 'Has Manager'

END AS hierarchy_status

FROM employees e

LEFT JOIN employees m ON e.manager_id = m.id

WHERE e.status = 'active'

ORDER BY e.department, e.name;

NULL manager_id is valid for CEO but invalid for others. CASE distinguishes valid NULL (CEO) from data quality issue. Self-join with LEFT JOIN preserves CEO in results. HR can identify employees with missing manager assignments.

All