News for hackers. Stuff that matters.
Re:A Critique of SQL, 40 Years Later by chasil on Wednesday August 24, 2022 @02:53PM (#32584058)
NULL itself is open to interpretation.

If I CREATE TABLE foo (bar CHAR(1) PRIMARY KEY, baz char(1) UNIQUE), then different things happen on different databases.

In Microsoft SQL server, only one insert of a null into the baz column is allowed, and the null value is indexed.

In Oracle, null is never indexed in this context, so any number of null insertions into baz are allowed.

On a composite index, I believe that nulls are always indexed, on any database (they must be).

As far as I understand it, this is implementation-defined:

  SQL> select * from dual where null=null;

  no rows selected
Re:A Critique of SQL, 40 Years Later by dspillett on Thursday August 25, 2022 @05:10AM (#32591353)
WHERE NULL=NULL should never return true, so no rows returned is correct. It is only implementation defined in that some implementations get this wrong!¹³

SQL Server's handling of NULLs with in unique indexes/constraints is not standard. You can get better using a filtered unique index (“CREATE UNIQUE INDEX more_compliant_unique_index ON dbo.some_table(this_column) WHERE this_column IS NOT NULL;”), but be aware that filtered indexes have their own collection of gotchas⁴.

----

[1] MS Access IIRC, MS SQL Server if the ANSI_NULLS option set to off, MS² SSIS's default (consider NULLS equal) behaviour for merge steps, and so on.

[2] You may be noticing a pattern here… MS are not the only culprit though.

[3] I'm making a distinction here between places where behaviour is not defined in the standards, so implementations are free to go their own way, and where implementations go against what is expected by the standards.

[4] particularly with respect to the collection of unfixed/wontfix gnarly edge cases that is the MERGE statement.