Are You Qualified to Use Null in SQL?
agentm.github.io
Are You Qualified to Use Null in SQL?
1–8 of 8 posts
Re: Are You Qualified to Use Null in SQL?
#2What database is this specific to? PostgreSQL?
Those first two are entirely invalid in T-SQL. Even attempting to write it as SELECT IIF(NULL AND 1=1, 1, 0) doesn't work. The AND operation can only be applied to a boolean type and the only way to get that is as the result of a comparison and it can't be stored or passed anywhere.
Also the UNIQUE constraint does not allow insert of a second NULL. I tried that both using the constraint syntax and by creating a unique index.
Re: Are You Qualified to Use Null in SQL?
#3What database is this specific to? PostgreSQL? Those first two are entirely invalid in T-SQL. Even attempting to write it as SELECT IIF(NULL AND 1=1, 1, 0) doesn't work. The AND operation can only be applied to a boolean type and the only way to get that is as the result of a comparison and it can't be stored or passed anywhere. Also the UNIQUE constraint does not allow insert of a second NULL. I tried that both usin…
In mysql, a unique constraint will not prevent multiple nulls in that column.
Re: Are You Qualified to Use Null in SQL?
#4What database is this specific to? PostgreSQL? Those first two are entirely invalid in T-SQL. Even attempting to write it as SELECT IIF(NULL AND 1=1, 1, 0) doesn't work. The AND operation can only be applied to a boolean type and the only way to get that is as the result of a comparison and it can't be stored or passed anywhere. Also the UNIQUE constraint does not allow insert of a second NULL. I tried that both usin…
Yeah, it looks like postgres to me. Nothing pops out as incompatible, and I got 17/22.
Re: Are You Qualified to Use Null in SQL?
#5What database is this specific to? PostgreSQL? Those first two are entirely invalid in T-SQL. Even attempting to write it as SELECT IIF(NULL AND 1=1, 1, 0) doesn't work. The AND operation can only be applied to a boolean type and the only way to get that is as the result of a comparison and it can't be stored or passed anywhere. Also the UNIQUE constraint does not allow insert of a second NULL. I tried that both usin…
In Postgres you can chose whether to treat NULLs as distinct or not (meaning that when treating NULLs as NOT DISTINCT you can insert many for an otherwise unique row).
If I’m not mistaken this is the docs page: https://www.postgresql.org/docs/current/indexes-unique.html
Re: Are You Qualified to Use Null in SQL?
#6This is null trivia, a lot of it is not worth memorizing because it's not important. Personally I was surprised that NULL AND FALSE does not result in NULL, but who cares?
Re: Are You Qualified to Use Null in SQL?
#7This is null trivia, a lot of it is not worth memorizing because it's not important. Personally I was surprised that NULL AND FALSE does not result in NULL, but who cares ?
It is not worth memorizing because it is implementation-dependent.
In Sybase derivatives (hello SQL Server), null can exist once in a unique index. A second insertion fails.
In Oracle, null is not indexed. In composite indexes, they are counted, but do not matter.
My score was 11/22. I guess that I vaguely know what I'm doing.
Re: Are You Qualified to Use Null in SQL?
#8This is null trivia, a lot of it is not worth memorizing because it's not important. Personally I was surprised that NULL AND FALSE does not result in NULL, but who cares ?
> a lot of it is not worth memorizing because it's not important.
Until it is.
If you're not familiar with how NULLs are compared, at least in mysql, it will bite you right in the rectum when you start setting up things like unique constraints on nullable columns.