MySQL: IS NOT NULL

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