**NULL and Index Usage:**

1. **IS NULL Queries:**

Some databases don't index NULL values by default:

SELECT * FROM orders WHERE customer_id IS NULL;

May require full table scan. Create partial index:

CREATE INDEX idx_null_customers ON orders(customer_id)

WHERE customer_id IS NULL; -- PostgreSQL

2. **NOT NULL Constraints Improve Performance:**

NOT NULL columns:

• Smaller indexes (no NULL entries)

• Faster comparisons (no NULL checks)

• Better query optimization

ALTER TABLE orders MODIFY customer_id INT NOT NULL;

3. **LEFT JOIN with NULL Foreign Keys:**

LEFT JOIN processes all left table rows:

SELECT * FROM orders o

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

If many orders have NULL customer_id:

• Database still processes them

• No customer lookup needed (NULL)

• But still slower than INNER JOIN

4. **Filtering NULL Early:**

Slow (filter after join):

SELECT * FROM orders o

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

WHERE o.customer_id IS NOT NULL;

Fast (filter before join):

SELECT * FROM orders o

INNER JOIN customers c ON o.customer_id = c.id;

-- INNER JOIN implicitly excludes NULL

5. **COALESCE Performance:**

COALESCE adds computation:

SELECT COALESCE(c.name, 'Unknown') AS name

FROM orders o

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

Use only when needed for display, not in WHERE/JOIN conditions.

**Best Practices:**

• Use NOT NULL constraints when relationship is required

• Create indexes on foreign keys (including NULL-aware indexes)

• Filter NULL early with WHERE or use INNER JOIN