5. MySQL: IS NULL and IS NOT NULL Operators

What is a NULL value?

1. A field with a NULL value is a field with no value

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

3. A NULL value differs from a zero value or a field containing spaces. A field with a NULL value has been left blank during record creation!

Points To Remember:

• The = NULL operator is not valid in MySQL and will result in a syntax error. Instead, you must use the IS NULL operator to test for null values.

• We cannot use comparison operators like =, <, <> to check for NULL values

IS NULL Operator:

The IS NULL operator is used to check for NULL/ empty values.

Syntax:

SELECT column_1, column_2, column_3
FROM table1
WHERE column_name IS NULL;

For our convenience, I have added a NULL value in the description in the film_text table so that we can get a result.

SELECT * 
FROM film_text
WHERE description IS NULL;

Output:

IS NOT NULL Operator:

The IS NOT NULL value is used to check for not NULL/ non-empty values.

Syntax:

SELECT column_1, column_2, column_3
FROM table1
WHERE column_name IS NOT NULL;

Example: We will just get all the values from the film_text table which are not null.

SELECT * 
FROM film_text
WHERE description IS NOT NULL;

Output:

Some practical uses of the NULL operator:

• Handling null values in aggregate functions: The IS NULL operator can be used in conjunction with aggregate functions such as SUM and AVG to exclude null values from the calculation

• Filtering null values: The IS NULL operator can be used in a WHERE clause of a SELECT statement to filter out rows with null values in a particular column.

We'll meet again tomorrow :)

*****All the outputs provided are a snippet of the actual result.

To see more examples, you can always refer w3 schools; it has always been my go-to website to understand things.

References: https://www.w3schools.com/mysql/default.asp