Description

The SQL keyword IS NOT NULL is used to test for a non-empty or NOT NULL value.

  • It returns TRUE if the value is non-empty or NOT NULL.
  • A NULL value is different from a zero value or a field containing spaces.
  • The NULL values cannot be validated with the comparison operators, like =, < , >, <>, etc.,
  • Always use IS NULL to validate a NULL value.

There is another operator IS NULL, which works exactly opposite IS NOT NULL operator.

SELECT With IS NOT NULL

The below SQL fetches all the customers having a NOT NULL value in the "city" column.

Run this on IDE

SELECT *
FROM customers
WHERE city IS NOT NULL;

UPDATE With IS NOT NULL

The below SQL updates the city column against all the customers having a NOT NULL value in the "city" column.

UPDATE customers
SET city = 'DUMMY CITY'
WHERE city IS NOT NULL;

DELETE With IS NOT NULL

The below SQL deletes all the customers having a NOT NULL value in the "city" column.

DELETE FROM customers
WHERE city IS NOT NULL;

Related Links