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 editor@searchDatabase.com 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.
This was first published in May 2001