First I tried to look at it as a "zero" (with a little confusion though) when I came across it in some queries, or think about it as a text string of blank spaces.
Null means either "not applicable" or "don't know": it's not really the same as zero (0) 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 -
| StudentName | TestResult |
|---|---|
| Samuel Jones | 78 |
| Hilary Smith | 89 |
| Jed Thomas | 67 |
| Lee Neal | NULL |
| Sally Lane | 72 |
As you can see, Lee's value is NULL, which could be interpreted as meaning that Lee did not sit the test (maybe he was off ill and will take the test another day). It would be wrong to assign a zero (0) to the test result, because that would be interpreted as Lee having taken the test and not getting a single answer right!
Now let's consider the following query -
SELECT AVG(TestResult) FROM Students
Aggregate functions like AVG() and SUM() ignore NULL's, so this query will return (78+89+67+72)/4=76.5, which is certainly better than (78+89+67+0+72)/5=61.2 which you'd get using a zero (0) 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 DateTerminated 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.
Further information can be found relating to NULL values in the following article: Defining Null Values