SQL – SELECT NULL

The word NULL is used to describe a missing value in SQL. In a table, a NULL value is a value in a field that appears to be empty. A field with a NULL value is the same as one that has no value. It’s important to grasp the difference between a NULL value and a zero value or a field of spaces.

There are two possibilities:

• Where SQL is NULL

Syntax: 
SELECT  *
FROM TABLANAME
WHERE COLUMNNAME IS NULL;

• Where SQL is NOT NULL

Syntax:  
SELECT *
FROM TABLANAME
WHERE COLUMNNAME IS NOT NULL;

NOT NULL denotes that the column must always consider an explicit value of the specified data type. We did not use NOT NULL in two columns, which means these columns may be NULL.  A field with a NULL value was left blank during the record creation process.

Example: 

Here, we will first create a database named “geeks” then we will create a table “department” in that database. After, that we will execute our query on that table.

Creating Database:

CREATE DATABASE geeks;

To use this database:

USE geeks;

To create a table in the geeks database:

CREATE TABLE [dbo].[department](
[ID] [int] NULL,
[SALARY] [int] NULL,
[NAME] [varchar](20) NULL
)
GO

Add value into the table:

INSERT INTO [dbo].[department] ( ID, SALARY, NAME) VALUES  ( 1, 34000, 'Neha')  
INSERT INTO [dbo].[department]( ID,  NAME) VALUES  ( 2,  'Hema')
INSERT INTO [dbo].[department]( ID, SALARY, NAME) VALUES  ( 3, 36000, 'Jaya' )
INSERT INTO [dbo].[department] ( ID,  NAME)VALUES  ( 4,  'Priya' )
INSERT INTO [dbo].[department]( ID, SALARY, NAME) VALUES  ( 5, 34000, 'Ketan' ))
GO

This is our data inside the table:

SELECT * FROM department;

Select where SQL is NULL:

SELECT * FROM department WHERE salary IS NULL;

Output:

Select where SQL is NOT NULL:

SELECT * FROM department WHERE salary IS NOT NULL;

Output: