nobody does. MySQL, Oracle, MSSQL, you name it. All sux. That's why I prefer to always declare NOT NULL and have a DEFAULT value when I create tables. Treat the default value as NULL and you'll increase performance a lot.
PostgreSQL will happily use an index when looking up nulls. Given an index over an integer column, a query for a constant non-null value appears like:
QUERY PLAN
---------------------------------------------------------------------
Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8)
Index Cond: (b = 333)
The exact same query plan is generated for a query looking for nulls:
QUERY PLAN
---------------------------------------------------------------------
Index Scan using foo_b_idx on foo (cost=0.29..8.30 rows=1 width=8)
Index Cond: (b IS NULL)
(That is, the condition on the scan is the only thing that differs.)
(I would heavily suspect that both MSSQL and MySQL have similar behavior here; this is an easy optimization for a query planner.)
And it has done so for many versions, 8 is just the default redirect I got. The very first sentence on that page is:
>MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value.
It works effectively everywhere, with the restriction that it can only do one null-optimized comparison per index (e.g. `(a == 1 or a is null) AND (b == 1 or b is null)` will only optimize one piece). Which is a potentially-significant restriction that makes me curious about the internals... but it does exist, and typical for MySQL is clearly documented.
