How do you handle NULL values in SQL?

Handling NULL values in SQL requires understanding how they behave in comparisons, calculations, and data operations. NULL represents missing or undefined data, and it doesn’t behave like typical values. For example, using WHERE column = NULL will not work because NULL is not equal to any value, including itself. Instead, use IS NULL or IS NOT NULL to check for NULLs. Similarly, aggregate functions like SUM() or AVG() ignore NULL values by default, which can lead to unexpected results if not accounted for. To replace NULLs with a default value, functions like COALESCE() (standard SQL) or ISNULL() (SQL Server) are useful. For instance, SELECT COALESCE(salary, 0) FROM employees replaces NULL salaries with 0, ensuring calculations like totals or averages include all rows.

When working with joins or constraints, NULLs can introduce subtle issues. For example, rows with NULL in a foreign key column won’t match any primary key, even if the primary key table has a NULL. Similarly, using NULL in a UNIQUE constraint allows multiple NULLs because NULL is considered distinct from other NULLs in this context. To enforce data integrity, define columns as NOT NULL during table creation if the data must always exist. For example, CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) NOT NULL) ensures every user has an email. When querying, avoid mixing NULLs and non-NULL values in conditional logic. For instance, WHERE (column = 5 OR column IS NULL) ensures both cases are covered explicitly.

Performance and indexing are also affected by NULLs. Some databases don’t index NULL values, so queries filtering for IS NULL might perform full table scans. To optimize, consider using computed columns or partial indexes that include NULLs if frequently queried. For example, in PostgreSQL, CREATE INDEX idx_missing_data ON orders (order_id) WHERE shipped_date IS NULL creates an index for unshipped orders. Always document how NULLs are treated in your schema to avoid confusion. Testing edge cases, like NULL in GROUP BY (where all NULLs group together) or ORDER BY (sorted first or last depending on the database), ensures consistent behavior. By anticipating these scenarios, you can design queries and schemas that handle NULLs predictably.