17. All about Null value & Keyword in SQL

In SQL Null is both a value as well as a keyword. Let's look into NULL value first -

Null as a Value

In simple terms, NULL is simply a place holder for data that does not exist. When performing insert operations on tables, they will be times when some  field values will not be available. 

In order to meet the requirements of true relational database management systems, MySQL uses NULL as the place holder for the values that have not been submitted. The screenshot below shows how NULL values look in database.

Let's now look at some of the basics for NULL before we go further into the discussion.

• NULL is not a data type - this means it is not recognized as an "int", "date" or any other defined data type.
• Arithmetic operations involvingNULL always return NULL for example, 69 + NULL = NULL.
• All aggregate functions affect only rows that do not have NULL values.

Let's now demonstrate how the count function treats null values.  Let's see the current contents of the members table-

?

1	SELECT * FROM `members`;

Executing the above script gives us the following results

Let's count all members who have updated their contact_number

?

1	SELECT COUNT(contact_number)  FROM `members`;

Executing the above query gives us the following results.

Note: Values that are NULL have not been included

What is NOT?

The NOT logical operator is used to test for Boolean conditions and returns true if the condition is false. The NOT operator returns false if the condition been tested is true

Condition	NOT Operator Result
True	False
False	True

Why use NOT null?

There will be cases when we will have to perform computations on a query result set and return the values. Performing any arithmetic operations on columns that have the NULL value returns null results. In order to avoid such situations from happening, we can employ the use of the NOT NULL clause to limit the results on which our data operates.

NOT NULL Values

Let's suppose that we want to create a table with certain fields that should always be supplied with values when inserting new rows in a table. We can use the NOT NULL clause on a given field when creating the table.

The example shown below creates a new table that contains employee's data. The employee number should always be supplied

?

1 2 3 4 5

CREATE TABLE `employees`(   employee_number int NOT NULL,   full_names varchar(255) ,   gender varchar(6) );

Let's now try to insert a new record without specifying the employee name and see what happens.

?

1	INSERT INTO `employees` (full_names,gender) VALUES ('Steve Jobs', 'Male');

Executing the above script in MySQL workbench gives the following error -

NULL Keywords

NULL can also be used as a keyword when performing Boolean operations on values that include NULL. The "IS/NOT" keyword is used in conjunction with the NULL word for such purposes. The basic syntax when null is used as a keyword is as follows

?

1 2	`comlumn_name'  IS NULL `comlumn_name' NOT NULL

HERE

• "IS NULL" is the keyword that performs the Boolean comparison. It returns true if the supplied value is NULL and false if the supplied value is not NULL.
• "NOT NULL" is the keyword that performs the Boolean comparison. It returns true if the supplied value is not NULL and false if the supplied value is null.

Let's now look at a practical example that uses the NOT NULL keyword to eliminate all the column values that have null values.

Continuing with the example above , suppose we need details of members whose contact number is not null . We can execute a query like

?

1	SELECT * FROM `members` WHERE contact_number IS NOT NULL;

Executing the above query gives only records where contact number is not null.

Suppose we want member records where contact number is null. We can use following query

?

1	SELECT * FROM `members` WHERE contact_number IS NOT NULL;

 

Executing the above query gives member details whose contact number is NULL

Comparing null values

Three-value logic - performing Boolean operations on conditions that involve NULL can either return "Unknown", "True" or "False".

For example, using the "IS NULL" keyword when doing comparison operationsinvolving NULL can either return true or false. Using other comparison operators returns "Unknown"(NULL).

Suppose you compare number five with 5

?

1	SELECT 5 =5;

The query result is 1 which means TRUE

Let's do the same operation with NULL

?

1	SELECT NULL = NULL;

 

Let's look at another example

?

1	SELECT 5 > 5;

 

The query result is 0 which means FALSE

Let's look at same example using NULL

?

1	SELECT NULL > NULL;

 

Lets use the IS NULL keyword

?

1	SELECT 5 IS NULL;

The query result is 0 which is FALSE

?

1	SELECT NULL IS NULL;

The query result is 1 which is TRUE

Summary NULL is a value place holder for optional table fields. MySQL treats the NULL value differently from other data types. The NULL values when used in a condition evaluates to the false Boolean value. The NOT logical operate is used to test for Boolean values and evaluates to true if the Boolean value is false and false if the Boolean value is true. The NOT NULL clause is used to eliminate NULL values from a result set Performing arithmetic operations on NULL values always returns NULL results. The comparison operators such as [, =, etc.] cannot be used to compare NULL values.