SQL Tutorial SQL Advanced SQL Database SQL References

SQL - IS NOT NULL Operator



A field with no value is called a field with NULL value. To test the non-NULL value of a field, SQL IS NOT NULL operator is used. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE.

Syntax

The syntax for using IS NOT NULL operator is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name IS NOT NULL;

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis2800
4KimAmsterdam3100
5RameshNew Delhi283000
6HuangBeijing282800

  • To select all records of the Employee table where Age is null, the SQL code is given below.

    SELECT * FROM Employee
    WHERE Age IS NULL;
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    3JoParis2800
    4KimAmsterdam3100
  • To fetch all records of the Employee table where Age is not null, the SQL code is mentioned below.

    SELECT * FROM Employee
    WHERE Age IS NOT NULL;
    

    This will produce the following result:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    5RameshNew Delhi283000
    6HuangBeijing282800
  • To delete all records of the Employee table where Age is null, the following SQL code can be used:

    DELETE FROM Employee
    WHERE Age IS NULL;
    
    -- see the result
    SELECT * FROM Employee;
    

    This will produce the following result:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    2MarryNew York242750
    5RameshNew Delhi283000
    6HuangBeijing282800

❮ SQL - Operators