MySQL: IS NOT NULL

This MySQL tutorial explains how to use the MySQL IS NOT NULL condition with syntax and examples.

Description

The MySQL IS NOT NULL condition is used to test for a NOT NULL value in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IS NOT NULL Condition in MySQL is:

expression IS NOT NULL

Parameters or Arguments

expression

The value to test if it is a not NULL value.

Note

• If expression is NOT a NULL value, the condition evaluates to TRUE.
• If expression is a NULL value, the condition evaluates to FALSE.

Example - With SELECT Statement

Here is an example of how to use the MySQL IS NOT NULL condition in a SELECT statement:

SELECT *

FROM contacts

WHERE last_name IS NOT NULL;

This MySQL IS NOT NULL example will return all records from the contacts table where the last_name does not contain a null value.

Example - With INSERT Statement

Here is an example of how to use the MySQL IS NOT NULL condition in an INSERT statement:

INSERT INTO contacts

(contact_id, contact_name)

SELECT account_no, supplier_name

FROM suppliers

WHERE category IS NOT NULL;

This MySQL IS NOT NULL example will insert records into the contacts table where the category does not contain a null value.

Example - With UPDATE Statement

Here is an example of how to use the MySQL IS NOT NULL condition in an UPDATE statement:

UPDATE contacts

SET status = 'completed'

WHERE last_name IS NOT NULL;

This MySQL IS NOT NULL example will update records in the contacts table where the last_name does not contain a null value.

Example - With DELETE Statement

Here is an example of how to use the MySQL IS NOT NULL condition in a DELETE statement:

DELETE FROM contacts

WHERE last_name IS NOT NULL;

This MySQL IS NOT NULL example will delete all records from the contacts table where the last_name does not contain a null value.