The author discusses how SQL treats NULL values differently in columns with UNIQUE constraints, allowing multiple NULL values because each NULL is considered a distinct value. Using examples in SQLite, Postgres, and MYSQL, the author shows that NULL values are not considered equal to each other but can be checked using the IS operator. To ensure uniqueness in columns with NULL values, the author suggests using a generated column or a partial index. The author also highlights the lack of availability of the SQL standards document and addresses common misconceptions about NULL values in SQL.
https://jirevwe.github.io/sql-nulls-are-weird.html