How does indexing work on columns with NULL values in SQL?

NULL values are generally included in B-tree indexes in most major databases like PostgreSQL, MySQL (InnoDB), and SQL Server, allowing efficient use of IS NULL and IS NOT NULL queries. 2. PostgreSQL, MySQL, and SQL Server all index NULLs in regular indexes, with unique indexes typically allowing one NULL unless using filtered indexes. 3. Function-based or filtered indexes, such as partial indexes in PostgreSQL or filtered indexes in SQL Server, can explicitly exclude NULLs, making IS NULL queries unable to use them. 4. Oracle does not store rows where all indexed columns are NULL in regular B-tree indexes, creating an exception for composite indexes. 5. Practical considerations include ensuring indexes support IS NULL queries, using default values instead of NULLs when possible, and leveraging filtered indexes for non-NULL data to improve performance. 6. Always verify indexing behavior with execution plans and consult specific database documentation, as behaviors can vary by system and version.

Indexing columns with NULL values in SQL works differently depending on the database system, but there are some consistent patterns across most major databases like PostgreSQL, MySQL (InnoDB), and SQL Server.

NULLs and B-tree Indexes

Most SQL databases use B-tree indexes by default. In a B-tree index, NULL values are generally included, but how they’re handled can vary:

• PostgreSQL: Includes NULLs in B-tree indexes. You can query using IS NULL or IS NOT NULL and still use the index efficiently.
• MySQL (InnoDB): Also stores NULLs in B-tree indexes. An index on a nullable column can be used for IS NULL conditions.
• SQL Server: Includes NULLs in non-unique indexes. However, unique indexes typically allow only one NULL (unless a filtered index is used to exclude NULLs).

So yes — NULLs are usually indexed, and queries filtering on WHERE column IS NULL can use an index, contrary to a common misconception.

When Indexes Might Skip NULLs

There’s one important exception: function-based or filtered indexes.

For example, in Oracle, a regular B-tree index does not store entries where all columns are NULL. But more importantly:

• If you create a partial index (PostgreSQL) or filtered index (SQL Server), you can explicitly exclude NULLs:

  -- PostgreSQL: Only index non-NULL values
  CREATE INDEX idx_non_null ON table (column) WHERE column IS NOT NULL;

  In this case, IS NULL queries won’t use the index.

• Some older or non-standard storage engines (like MyISAM in MySQL) may have different behaviors, but modern systems handle NULLs reasonably well.

Practical Implications

Here’s what you need to know when working with NULLs and indexes:

• ✅ WHERE column IS NULL can use an index if the index includes NULLs (which it usually does).
• ✅ WHERE column = value OR column IS NULL may use an index, but performance depends on query planner and selectivity.
• ⚠️ Composite indexes: If a composite index includes nullable columns, rows where all indexed columns are NULL might not be indexed in some databases (especially Oracle).
• ⚠️ Unique indexes: Most databases treat NULLs as “not equal” to each other, so multiple NULLs are allowed in a unique index unless the database enforces stricter rules.

Tips for Better Performance with NULLs

• If you frequently query IS NULL, make sure the column is indexed.
• Consider using a default value (like an empty string or special sentinel) if NULLs aren’t semantically necessary — sometimes this simplifies indexing.
• Use filtered/partial indexes to exclude NULLs if you only care about non-NULL data:

  CREATE INDEX idx_active_users ON users (email) WHERE deleted_at IS NULL;

Basically, don’t assume NULLs are unindexed — in most cases, they are. But always check your specific database documentation and execution plans to be sure.

The above is the detailed content of How does indexing work on columns with NULL values in SQL?. For more information, please follow other related articles on the PHP Chinese website!