SQL SELECT NULL

What is NULL in SQL?

NULL denotes missing or unknown. SQL NULL refers missing or unknown value in a column of a table.

Note that zero (‘0’) and NULL are not same, they are completely different.

Regarding NULL there could be two conditions:

  1. SQL NULL
  2. SQL NOT NULL

We will check each of these in details.

SELECT SQL NULL

During the table creation if you make a column nullable, you can easily exclude that column while inserting data into the other columns. If you do not specify NOT NULL exclusively in the column attribute, the column is said nullable.

Let’s consider a Student table with the following data.

Roll_no First_Name Last_Name Marks
101 Sagar Sharma 85
102 Subrata Das  
103 Piyush  Deb 71
104 Ranjit  Bera  
105 Shankar Kumar 78

You can that for Roll No 102 and 104 marks are missing that means these are NULL values.

How to select the records where only NULL value exists?

The below query returns only those records which has null value in their marks column.

SELECT * FROM Student
WHERE Marks IS NULL;

It will returns the following records.

Roll_no First_Name Last_Name Marks
102 Subrata Das  
104 Ranjit  Bera  

SQL SELECT NOT NULL

You can also select those records which are having only NOT NULL values in a particular column. In our case case we will select only those records which have NOT NULL values in their Marks column.

SELECT * FROM Student
WHERE Marks IS NOT NULL;

The above query returns the following records.

Roll_no First_Name Last_Name Marks
101 Sagar Sharma 85
103 Piyush  Deb 71
105 Shankar Kumar 78

Summary: In this tutorial, you have learned what is NULL and NOT NULL values in SQL and how to select them from a table when needed.

Leave a Comment