NUll Value in SQL

In SQL, Null value in SQL is unassigned, inaccessible and undefined value.

NULL represents missing or unknown data. By default, table column holds the NULL value. If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

 

NULL is used as a placeholder for unknown or inapplicable values.

NULL is not 0, they are not equivalent.

 

Working with NULL Value in SQL

Let’s consider the following ‘Employees’ table.

SQL> Select Last_name, Commission_pct from Employees;

Null in SQL

 

Suppose that the “Commission_Pct” column in the “Employees” table is optional. This means that if we insert a record with no value for the “Commission_Pct” column, the “Commission_Pct” column will be saved with a NULL value.

 

How can we test for NULL value in SQL?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

 

IS NULL in SQL :

We use IS NULL operator to Select only Null values from the database.

SQL > SELECT First_name, Commission_PCT from Employees where Commission_PCT IS NULL;

This query will return the Last_Name of employees, for whom Commission_Pct is Null.

Always use IS NULL to look for NULL values.

 

IS NOT NULL in SQL :

We use IS NOT NULL operator to Select non-null values in the Database.

SQL > SELECT First_name, Commission_PCT from Employees where Commission_PCT IS NULL;

This query will return the Last_Name of employees, for whom Commission_Pct is not Null.

0 Comment

Leave a Reply