SQL Server IS NULL and IS NOT NULL Queries

SQL Server NULL Keyword

In SQL Server, NULL is an unknown value or a value for which data is not available.

IS NULL can be used in the WHERE clause to retrieve the NULL value from the table

Result is always NULL when NULL value is compared with any other value using comparison operators or any calculation is performed on a NULL value.

We cannot compare one NULL value with another, no two NULL values are equal.

In ascending order, NULL values are the first precedence to be displayed.

IS NULL Syntax

SELECT column_name1[, column_name2, ....]
FROM table_name
WHERE column_name IS NULL

here getting rows which column_name field has NULL value.

IS NOT NULL Syntax

SELECT column_name1[, column_name2, ....]
FROM table_name
WHERE column_name IS NOT NULL

here getting rows which column_name field has non NULL value.

Department Table

Let us consider the example 'Department' database table.

IS NULL Example

To get the rows which Head column value is null in Department table.

SELECT Id, Name, Head 
FROM Department 
WHERE Head IS NULL

IS NOT NULL Example

To get the rows which Head column value is not null in Department table.

SELECT Id, Name, Head 
FROM Department 
WHERE Head IS NOT NULL