In MySQL, NULL is a special marker used to indicate that a data value does not exist in the database. It represents the absence of a value in a column.
We can use the IS NULL or IS NOT NULL operators to check for NULL values in a column.
-- Retrieve rows where the 'column_name' is NULL SELECT * FROM table_name WHERE column_name IS NULL;
-- Retrieve rows where the 'column_name' is NOT NULL SELECT * FROM table_name WHERE column_name IS NOT NULL;
When inserting data into a table, we can explicitly insert NULL values into columns that allow it.
INSERT INTO table_name (column1, column2, column3) VALUES (1, NULL, 'some value');
We can update existing rows to set a column to NULL using the UPDATE statement.
UPDATE table_name SET column_name = NULL WHERE condition;
When working with expressions that involve columns that might have NULL values, we can use the "COALESCE" function to provide a default value in case of "NULL".
SELECT COALESCE(column_name, 'Default') AS modified_column FROM table_name;