SQL's use of "NULL" is inherently flawed as the relational model does not work with "NULL" values as implemented by SQL. Codd himself (in "The Relational Model for Database Management: Version 2) suggested that the SQL implementation of NULL was flawed and should be replaced by two distinct NULL-type markers. The markers he proposed were to stand for "Missing but Applicable" and "Missing but Inapplicable", known as A-values and I-values, respectively. Codd's recommendation, if accepted, would have required the implementation of a four-valued logic in SQL. None of the common RDBMs in use do this and neither does any SQL standard.

Of course, the SQL standard is a flawed implementation of the relational model in any case, but as it is "the best" (meaning the only one we have), we will have to live with it.

To give an example of how flawed the use of "NULL" is, consider the following:

1. A database table T with two columns: ID-number and Amount.
2. The following query: SELECT ID-number, Amount FROM T WHERE Amount = 10 OR NOT(Amount = 10)
3. This query should return ALL rows, since either the amount is equal to 10 or it is not. It does not matter whether the amount is actually unknown: it can only be "10" or "not 10". Yet this query will not return a row where "Amount = NULL".

I agree that "NULL" comes in handy in using as a placeholder for an unknown value, but in many cases the use of "NULL" indicates that we have not been thinking the design of our database through in all its aspects and that something should be changed there.

You trying to prove it shouldn't be used by saying we should use two different types of it.

You trying to prove it shouldn't be used by saying it's handy.

You explain that it's flawed, but something being flawed shouldn't necessarily be avoided.

To say the least, you haven't made your point.

The two different types of "NULL" solve the problems the one "NULL" causes.

It may be handy in its implemention, but it is heresy in the model.

As the implementation of the relational model (i.e. SQL) is already flawed in several respects if one looks at the requirements of the relational model, you cannot mend it simply by avoiding to use "NULL", but neither should one compound the problems by an indiscriminate use of "NULL".

I wasn't trying to "make a point", I just hoped to explain that the use of "NULL" is not free from problems.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

I have to disagree. Null as a place holder for an unknown value is a perfectly acceptable use of null. You've contrived an example that only shows how flawed the design was to allow null values into the database in the first place. It's not the 'null' or the 'select' that fails to count null values at fault, it's the table definition that allows for null values to be entered when none are expected to be there. If nulls are not part of your world then don't create tables that allow null values to be entered.

It seems to me that the Closed World Assumption demands that your example must return the "plainly wrong" result that it does:

This is the so-called "Closed World Assumption": Everything stated by the database, either explicitly or implicitly, is true; everything else is false.

The database in your example does not state, either explicitly or implicitly, that Amount is 10, therefore this is false. It also does not state that Amount is not 10, therefore this is also false.

The database in your example does not state, either explicitly or implicitly, that Amount is 10, therefore this is false. It also does not state that Amount is not 10, therefore this is also false.

In the real world, you cannot have an amount that is at the same time not 10 and not not 10. It has to be one of both. The amount is perhaps unknown (if that is what you mean to express by having a "NULL"-value in this field). But even if it is unknown, it has to be 10 or not 10 (but we do not know which one). So a query that asks for all rows where the amount is 10 or the amount is not 10, must return all rows. SQL does not return the rows where the amount is "NULL" and that is where it is flawed and gives a wrong result. The relational model cannot work with a "NULL"-value. If you want to have a value for "unknown", you must have TWO values for unknown (as per CODD).

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James