Null Operators In MySQL

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.

In MySQL, 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 MySQL:

Checking for NULL Values:

We can use the IS NULL or IS NOT NULL operators to check for NULL values in a column.

Syntax: IS NULL

-- Retrieve rows where the 'column_name' is NULL
SELECT *
FROM table_name
WHERE column_name IS NULL;

Example: IS NULL

SELECT *
FROM products
WHERE product_description IS NULL;

The above query retrieves all rows from the `products` table where the 'product_description' is NULL.

It returns true if the column value is null, otherwise, it returns false.

Syntax: IS NOT NULL

-- Retrieve rows where the 'column_name' is NOT NULL
SELECT *
FROM table_name
WHERE column_name IS NOT NULL;

Example: IS NOT NULL

SELECT *
FROM products
WHERE product_description IS NOT NULL;

The above query retrieves all rows from the `products` where the 'product_description' is NOT NULL.

It returns true if the column value is not null, otherwise, it returns false.

Inserting NULL Values:

When inserting data into a table, we can explicitly insert NULL values into columns that allow it.

Syntax:

INSERT INTO table_name (column1, column2, column3)
VALUES (1, NULL, 'some value');

Example:

INSERT INTO products (product_name, product_description, category, product_price)
VALUES ("IPad", NULL, 'HEALTH_CARE', 400);

Updating NULL Values:

We can update existing rows to set a column to NULL using the UPDATE statement.

Syntax:

UPDATE table_name
SET column_name = NULL
WHERE condition;

Example:

UPDATE products
SET product_price = NULL
WHERE category = "HEALTH_CARE";

Handling NULL Values in Expressions:

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".

Syntax:

SELECT COALESCE(column_name, 'Default') AS modified_column
FROM table_name;

The above query selects the column values, replacing NULL values with the string 'Default'.

<-- In & Between

Update -->