NULL in SQL really isn't great. For one, nullable table columns is a bad default, and you have to explicitly write out "NOT NULL" to avoid this behavior. I'd say that 90% of the time I want not-null table columns, and only 10% of the time do I want a nullable column.
Secondly, NULL has weird arithmetic. It turns out that NULL=NULL is false, and NULL<>NULL is also false. (This is unlike C/Java/Python/etc. by the way.)
Thirdly, even if you design all your tables to have NOT NULL on all columns, your queries can still synthesize NULL values in the results. For example, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, (but not INNER JOIN). For example, computing max(column) on a table with zero rows.
I can get behind your first statement. Having NULLable as a default on columns is "probably" a bad idea.
I'm not so sure I can agree with the other two. NULL<>NULL (and NULL=NULL) both return false for a very simple reason: truly missing data _can't_ be equal to anything, including missing data... Because it's missing. You cannot with certainty say that value1 is or is not equal to each other.
For the third point... What should max(column) return when there's no data? You're telling the engine "give me the maximum value of something that doesn't exist". That is, in my experience, "missing data."
For example, if it were the case that NULL = NULL, really counterintuitive stuff would happen on joins because a null cell would match with every other null cell you are joining on:
person
name home_address
---------------------------
"Alice" NULL
"Bob" "123 Jump Street"
letter
return_address description
----------------------------------
NULL "Ransom Letter"
NULL "Spy Document"
"123 Jump Street" "Hello, from Bob"
Then
SELECT name, description FROM person INNER JOIN letter ON home_address = return_address
would return
name description
------------
"Alice" "Ransom Letter"
"Alice" "Anonymous Spy Document"
"Bob" "Hello from Bob"
So now Alice is associated with a bunch of letters she didn't necessarily write because she doesn't have a home address.
> NULL in SQL really isn't great. For one, nullable table columns is a bad default, and you have to explicitly write out "NOT NULL" to avoid this behavior.
This is not true on many dbms. It's an implementation choice.
Secondly, NULL has weird arithmetic. It turns out that NULL=NULL is false, and NULL<>NULL is also false. (This is unlike C/Java/Python/etc. by the way.)
Thirdly, even if you design all your tables to have NOT NULL on all columns, your queries can still synthesize NULL values in the results. For example, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, (but not INNER JOIN). For example, computing max(column) on a table with zero rows.