What does null mean?

Dear Rudy,
I've just started self-studying MSSql 7.0, so I hope you don't mind if I come up with a stupid question: What exactly does "null" mean? First I tried to look at it as a "zero" (with a little confusion though) when I came across it in some queries. Then, I encountered this term again in the connection tab of the sql server's property. This time it says "ansi null." I can't understand the relation between American National Standard Institude and "null". I wish you could give me a better explanation.

Null means either "don't know" or "not applicable" -- it's not really the same as zero or any other default value, but more importantly, null is treated quite differently from other values in SQL, because it literally has no value.

Here's an example of a "don't know" null --

Student  TestResult
  Joe       87
  Bill      73
  Mary      56
  Fred     null
  Sam       92

As you can see, Fred's value is null, which you could interpret as meaning that Fred didn't write the test (maybe he has a medical exemption and will take the test another day). It would be wrong to assign a zero, because that would be interpreted as Fred having taken the test and not getting a single answer right!

Now consider the following query --

SELECT AVG(TestResult)
  FROM Students

Aggregate functions like AVG() and SUM() ignore nulls, so this query will return (87+73+56+92)/4=77, which is certainly better than 87+73+56+0+92)/5=61.6 which you'd get using a zero default. Often a default value is just wrong for a column where you expect to take aggregates.

An example of a column that would take a "not applicable" null is Date Terminated in a human resources database, where the value would be null for all active employees. To test for nulls, you can filter them out in the WHERE clause --

SELECT EmployeeID
     , (DateTerminated - DateHired) AS LengthOfService
  FROM EmployeeTable
WHERE DateTerminated IS NOT NULL

which would give results only for terminated employees. If you didn't have the WHERE clause, the above query would return null for every active employee, because any expression involving a null yields a null result.

Alternatively, you can use the COALESCE function to supply a non-null value --

SELECT EmployeeID
     , ( COALESCE(DateTerminated,GETDATE())
         - DateHired) AS LengthOfService
  FROM EmployeeTable

where GETDATE() returns today's date and therefore provides an accurate measure for the length of service of active employees. So for terminated employees, DateTerminated is not null, and the calculation is the same as above, while for active employees, DateTerminated is null so COALESCE uses today's date instead.

For More Information

• What do you think about this answer? E-mail us at [email protected] with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.