SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

SQL Server - IS NULL Operator



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

Syntax

The syntax for using IS NULL operator in SQL Server (Transact-SQL) is given below:

SELECT column1, column2, column3, ...
FROM table_name
WHERE column_name IS 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 query 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 query 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 query 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 Server - Operators