A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
Inserting NULL Values: When inserting data into a table, if a column is defined to allow NULLs, you can explicitly insert a NULL value by omitting a value for that column in the INSERT statement or by explicitly specifying the keyword NULL.
INSERT INTO table_name (column1, column2) VALUES (value1, NULL);
Checking for NULL Values: You can check for NULL values using the IS NULL operator or the IS NOT NULL operator in a WHERE clause.
SELECT * FROM table_name WHERE column_name IS NULL;
SELECT * FROM table_name WHERE column_name IS NOT NULL;
Comparing with NULL Values: Comparisons with NULL values using comparison operators such as =, <>, <, >, <=, and >= always return UNKNOWN instead of TRUE or FALSE. To compare with NULL, you should use the IS NULL or IS NOT NULL operators.
Aggregating NULL Values: Aggregate functions like COUNT(), SUM(), AVG(), MIN(), and MAX() typically ignore NULL values unless explicitly stated otherwise using the DISTINCT keyword.
SELECT COUNT(column_name) FROM table_name; -- Excludes NULL values
SELECT COUNT(DISTINCT column_name) FROM table_name; -- Includes NULL values
Handling NULL Values in Expressions: NULL values propagate through expressions. Any arithmetic operation involving a NULL value results in NULL.
Using Coalesce to Replace NULL Values: The COALESCE() function allows you to replace NULL values with a specified replacement value.
SELECT COALESCE(column_name, replacement_value) FROM table_name;
Dealing with NULL Values in Joins: NULL values can affect the outcome of joins. You may need to consider NULL values in join conditions to avoid unexpected results.
Storing and Retrieving NULL Values: SQL databases store NULL values efficiently, and they can be retrieved and manipulated like any other data value in the database.