Null in sql | NULL command in SQL - sql - sql tutorial - learn sql
- The SQL NULL is the term used to represent a missing value.
- A NULL value in a table is a value in a field that appears to be blank.
- A field with a NULL value is a field with no value.
- It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.
- NULL means that data does not exist.
- NULL is not equal to 0 or an empty string.
- Both zero and empty string represent a value, while NULL has no value.
- Any mathematical operations performed on NULL will result in NULL.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialIS NULL Syntax
IS NULL Syntax
IS NOT NULL Syntax
- 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.
Table cost_Data
Store _Name | Sales |
---|---|
Store A | 300 |
Store B | 200 |
Store C | 100 |
Store D | NULL |
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialBelow are the results for each aggregate function:
Below are the results for each aggregate function:
Result:
SUM (cost) | AVG (cost) | MAX (cost) | MIN (cost) | COUNT (cost) |
---|---|---|---|---|
600 | 200 | 300 | 100 | 3 |
- 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.