In SQL, 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 from the column.
In SQL, The "IS NULL" and "IS NOT NULL" Operators are used to check whether a column contains null values or not.
Use "IS NULL" and "IS NOT NULL" Operators to enforce data integrity constraints, ensuring that column fields with important information are not left vacant.
Here are some important considerations when working with "NULL" values in SQL:
We can use the "IS NULL" Operators to check for "NULL" values in a column.
SELECT * FROM tableName WHERE columeName IS NULL;
The below query retrieves all rows where the 'product_description' is NULL.
SELECT * FROM products WHERE product_description IS NULL;
It returns true if the column value is null, otherwise, it returns false.
We can use "IS NOT NULL" Operators to check values not "NULL" in a column.
SELECT * FROM tableName WHERE columnName IS NOT NULL;
The below query retrieves all rows where the 'product_description' is NOT NULL.
SELECT * FROM products WHERE product_description IS NOT NULL;
It returns true if the column value is not null, otherwise, it returns false.
When inserting data into a table, we can explicitly insert NULL values into columns that allow it.
INSERT INTO products (product_code, product_description, product_name) VALUES (1, NULL, 'Mobile Phones');
We can update existing rows to set a column to "NULL" using the UPDATE statement.
UPDATE tableName SET columnName = NULL WHERE condition;
The below query update `product_description` to `NULL` where the 'product_code' is `A101`.
UPDATE products SET product_description = NULL WHERE product_code = "A101"
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(columnName, 'Default') AS modified_column FROM tableName;