Null Value

Null Value In Oracle SQL

Topic Introduction: In this tutorial, we will discuss Null which is a value that is unavailable, unassigned, unknown, or inapplicable. Null is not the same as zero or a blank space. Details descriptions and examples have been given below for more clarification. 

SELECT last_name, job_id, salary, commission_pct

FROM employees;

When a data row lacks a value for a specific column, we refer to that value as 'null.' Null denotes a state of unavailability, unassignment, uncertainty, or inapplicability. It's essential to understand that null is distinct from zero or a blank space. Zero is a numeric value, and a blank space is a character.

Columns of any data type can potentially contain null values, although certain constraints, such as 'NOT NULL' and 'PRIMARY KEY,' prevent the use of nulls within a column.

For instance, consider the 'COMMISSION_PCT' column in the 'EMPLOYEES' table. In this context, a null value signifies that only sales managers or sales representatives are eligible to earn a commission, while other employees are not entitled to commissions.

Null Values in Arithmetic Expressions

SELECT last_name, job_id, salary, commission_pct, salary*commission_pct

FROM employees;

When any column value in an arithmetic expression is null, the result will also be null. For instance, attempting to divide by zero will lead to an error. Similarly, when you divide a number by null, the result becomes null or is considered unknown.

In the example presented in the slide, certain employees do not receive any commission. This occurs because the 'COMMISSION_PCT' column within the arithmetic expression is null, resulting in a null outcome.