SQL - NULL Value

You may not insert data to all the columns of a table in the database. If a column defined as NULL column, that means the value of that column can be empty. You are free to insert or update data anytime you want.

To fetch the NULL values from a table, we can use keywords, NULL or NOT NULL. You can not select NULL data of a table by using any comparison operators (e.g. =, !=, >, < ). The special clause IS NULL or IS NOT NULL is needed to check it.

For the demo purpose, the following Employee table will be used in all examples here.

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '650.127.1834' 33000
2 'James' 'Bond' '123.456.4568'
3 'Neena' 'Kochhar' '[email protected]' 17000
4 'Lex' 'De Haan' '[email protected]' '123.456.4569' 15000

The following example will select employees with PhoneNo as NULL.

SQL Script: IS NULL
SELECT * FROM Employee WHERE PhoneNo IS NULL;

The above query will display the following result.

EmpId FirstName LastName Email PhoneNo Salary
3 'Neena' 'Kochhar' '[email protected]' 17000

The following query uses IS NOT NULL to return data whose Email value is not NULL.

SQL Script: IS NOT NULL
SELECT * FROM Employee WHERE Email IS NOT NULL;

The above query will display the following result.

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '650.127.1834' 33000
3 'Neena' 'Kochhar' '[email protected]' 17000
4 'Lex' 'De Haan' '[email protected]' '123.456.4569' 15000

Update the NULL value using the UPDATE statement, as shown below.

SQL Script: IS NOT NULL
UPDATE Employee SET Salary = NULL
WHERE EmpId = 1;

The above query will display the following result.

EmpId FirstName LastName Email PhoneNo Salary
1 'John' 'King' '[email protected]' '650.127.1834'
2 'James' 'Bond' '123.456.4568'
3 'Neena' 'Kochhar' '[email protected]' 17000
4 'Lex' 'De Haan' '[email protected]' '123.456.4569' 15000