SQL Defining a Null Value

Defining a Null Value

Null is a value that is unavailable, unassigned, unknown,or inapplicable.

Null is not the same as zero or a blank space.

If a row lacks a data value for a particular column, that value is said to be null or to contain a null.

Null is a value that is unavailable, unassigned, unknown, or inapplicable. Null is not the same as zero

or a blank space. Zero is a number and blank space is a character.

Columns of any data type can contain nulls. However, some constraints (NOT NULL and PRIMARY

KEY) prevent nulls from being used in the column.

            SELECT last_name, job_id, salary, commission_pct FROM employees;

               

In the COMMISSION_PCT column in the EMPLOYEES table, notice that only a sales manager or

sales representative can earn a commission. Other employees are not entitled to earn commissions. 

A null represents that fact.

Null Values in Arithmetic Expressions

Arithmetic expressions containing a null value evaluate to null.

If any column value in an arithmetic expression is null, the result is null. For example, if you attempt

to perform division by zero, you get an error. However, if you divide a number by null, the result is a

null or unknown.

            SELECT last_name, 12*salary*commission_pct FROM employees;

In the example employee King does not get any Commission.Because the COMMISSION_PCT column in the arithmetic expression is null so the result is null.