In SQL, NULL means that data does not exist. NULL does not equal to 0 or an empty string. Both 0 and empty string represent a value, while NULL has no value.
Any mathematical operations performed on NULL will result in NULL. For example,
10 + NULL = NULL
Aggregate functions such as SUM, COUNT, AVG, MAX, and MIN exclude NULL values. This is not likely to cause any issues for SUM, MAX, and MIN. However, this can lead to confusion with AVG and COUNT.
Let's take a look at the following example:
Below are the results for each aggregate function:
SELECT SUM (Sales), AVG (Sales), MAX (Sales), MIN (Sales), COUNT (Sales)
|SUM (Sales)||AVG (Sales)||MAX (Sales)||MIN (Sales)||COUNT (Sales)|
Note that the AVG function counts only 3 rows (the NULL row is excluded), so the average is 600 / 3 = 200, not 600 / 4 = 150. The COUNT function also ignores the NULL row, which is why COUNT (Sales) = 3.
Next: SQL ISNULL Function
|| Copyright © 2013 1keydata.com All Rights Reserved.