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:

Table Sales_Data

Store A300
Store B200
Store C100

Below are the results for each aggregate function:

SELECT SUM (Sales), AVG (Sales), MAX (Sales), MIN (Sales), COUNT (Sales)
FROM Sales_Date;


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 © 2016   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact