In SQL, a NULL value is a special marker used to indicate that a data value does not exist in the database.
This is different from a value of 0 or an empty string, which are considered to be actual values.
NULL values are often used to represent missing or unknown data.
Handling NULL Values
When working with NULL values in SQL, it’s important to understand how they are handled in comparison to other values.
In most cases, a comparison between a NULL value and any other value will return false.
For example, the following query will return no rows:
SELECT * FROM table WHERE column = 0;To handle NULL values in comparisons, you can use the IS NULL or IS NOT NULL operators.
The IS NULL operator returns true if a value is NULL, and the IS NOT NULL operator returns true if a value is not NULL.
For example, the following query will return all rows where the column is NULL:
SELECT * FROM table WHERE column IS NULL;Coalesce and Ifnull Functions
Another way to handle NULL values is by using the COALESCE and IFNULL functions.
These functions take two or more arguments and return the first non-NULL argument.
The COALESCE function is standard SQL and can take any number of arguments, while the IFNULL function is specific to certain SQL databases and takes only two arguments.
For example, the following query will return the value of column1 if it’s not NULL, otherwise it will return the value of column2:
SELECT COALESCE(column1, column2) FROM table;And the below query will do the same thing as above, but it’s specific to MySQL:
SELECT IFNULL(column1, column2) FROM table;NULL in Aggregate Functions
When using aggregate functions such as SUM, COUNT, and AVG, NULL values are generally ignored.
For example, the following query will return the total number of non-NULL values in the column:
SELECT COUNT(column) FROM table;If you want to include NULL values in the aggregate function, you can use the COUNT(*) function instead.
For example, the following query will return the total number of rows in the table, including those with NULL values:
SELECT COUNT(*) FROM table;Updating and Inserting NULL Values
To insert or update a NULL value in a table, you can simply use the keyword NULL in your SQL statement.
For example, the following query will insert a new row with a NULL value in the column:
INSERT INTO table (column) VALUES (NULL);And the following query will update the column to a NULL value:
UPDATE table SET column = NULL WHERE id = 1;Conclusion
NULL values can add complexity to SQL queries, but with a good understanding of how they are handled, you can effectively work with them.
Remember to use the IS NULL and IS NOT NULL operators for comparisons, the COALESCE and IFNULL functions for handling NULL values, and the COUNT(*) function for including NULL values in aggregate functions.
Keep in mind that NULL is different from empty string or zero.
