|
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
|
