Giá trị `NULL` có được lưu trong index không? `WHERE col IS NULL` có dùng được index không?

Tuỳ database, và đây là chỗ hay nhầm khi chuyển giữa các hệ.

PostgreSQL: B-tree có lưu NULL, nên WHERE col IS NULL dùng được index bình thường. Điều đó cũng có nghĩa index bao phủ mọi dòng của bảng, kể cả dòng NULL.

Oracle: B-tree không lưu dòng mà mọi cột trong index đều NULL. Vì vậy IS NULL không dùng được index đơn cột, và SELECT count(*) cũng không dùng được index đó. Mẹo cũ là thêm một hằng số làm cột thứ hai (CREATE INDEX ... ON t (col, 1)) để buộc entry NULL được lưu.

Unique index và NULL: theo chuẩn SQL, NULL không bằng NULL, nên unique index cho phép nhiều dòng cùng NULL. Đây là hành vi thường gây bất ngờ với soft delete:

sql

-- does not enforce what you expect: NULL never conflicts with NULL,
-- so many soft-deleted rows can share the same email
CREATE UNIQUE INDEX uq_users_email ON users (email, deleted_at);

-- reliable alternative: unique only among live rows
CREATE UNIQUE INDEX uq_users_email_live ON users (email)
WHERE deleted_at IS NULL;

PostgreSQL 15 trở lên có UNIQUE NULLS NOT DISTINCT để coi các NULL là trùng nhau, nhưng partial unique index vẫn là cách rõ ràng và tương thích rộng hơn.

Xem toàn bộ Database Essentials cùng filter theo level & chủ đề con.

Mở danh sách Database Essentials

Tuỳ database, và đây là chỗ hay nhầm khi chuyển giữa các hệ.

sql

-- does not enforce what you expect: NULL never conflicts with NULL,
-- so many soft-deleted rows can share the same email
CREATE UNIQUE INDEX uq_users_email ON users (email, deleted_at);

-- reliable alternative: unique only among live rows
CREATE UNIQUE INDEX uq_users_email_live ON users (email)
WHERE deleted_at IS NULL;

PostgreSQL 15 trở lên có UNIQUE NULLS NOT DISTINCT để coi các NULL là trùng nhau, nhưng partial unique index vẫn là cách rõ ràng và tương thích rộng hơn.