SQL > NULL

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_NameSales
Store A300
Store B200
Store C100
Store DNULL

Below are the results for each aggregate function:

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

Result:

SUM (Sales)AVG (Sales)MAX (Sales)MIN (Sales)COUNT (Sales)
6002003001003

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