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
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT 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.
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:
SELECT SUM (cost), AVG (cost), MAX (cost), MIN (cost), COUNT (cost)
FROM cost _Date;
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.