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_Name  Sales 
 Store A 300 
 Store B 200 
 Store C 100 
 Store D NULL 

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

This page was last updated on June 19, 2023.

Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact