Home » SQL

SQL query to find fields with NULL values

Here, we are going to learn how to find fields with NULL values? How to get records whose specific column has the NULL values?
Submitted by Abhishek Goel, on April 26, 2020

In SQL NULL values are those fields that do not contain any specific value.

But we should remember that the NULL value is different from the zero value or just space value (" "). Field with NULL value is the field that is left blank at the time of database creation.

Let us consider the following dataset and try to find out the NULL field present in it.

Table: Employee

EmpID	FirstName	LastName	Design	Salary
101	Ravi	Gupta	President	5000
202	Kishan	Saini	Manager	4000
303	Usha	Mittal	Sr. Analyst	3500
404	Monnie	Saxena	NULL	3000
505	Mahesh	Aggarwal	Developer	4500
606	Parveen	Garg	Clerk	3000
707	Mukul	Sharma	NULL	2500
808	Bhavesh	Joon	Salesman	2500

Testing of NULL value is not possible with the help of >, <, = signs rather we have to use IS NULL or NOT NULL operators.

Let us first talk about the syntax.

Syntax:

    SELECT column_names
    FROM table_name
    WHERE column_name IS NULL;

Now let's work upon the demo database provided above

SQL Query:

SELECT FirstName, LastName, Design
FROM Employee
WHERE Design IS NULL;

Output:

FirstName	LastName	Design
Monnie	Saxena	NULL
Mukul	Sharma	NULL

Note: Always remember to use the IS NULL operator, to check for Null Fields in the Table of the database.