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 involving NULL 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-
SELECT * FROM `members`;
Executing the above script gives us the following results
membership_ number full_ names gender date_of_ birth physical_ address postal_ address contact_ number 1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm 2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL jj@fstreet.com 3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 rm@tstreet.com 4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL 5 Leonard Hofstadter Male NULL Woodcrest NULL 845738767 NULL 6 Sheldon Cooper Male NULL Woodcrest NULL 976736763 NULL 7 Rajesh Koothrappali Male NULL Woodcrest NULL 938867763 NULL 8 Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL 9 Howard Wolowitz Male 24-08-1981 SouthPark P.O. Box 4563 987786553 lwolowitz[at]email.me
Let's count all members who have updated their contact_number
SELECT COUNT(contact_number) FROM `members`;
Executing the above query gives us the following results.
| COUNT(contact_number) |
|---|
| 7 |
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
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.
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
`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
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
SELECT * FROM `members` WHERE contact_number IS NULL;
Executing the above query gives member details whose contact number is NULL
membership_ number full_names gender date_of_birth physical_address postal_address contact_ number 1 Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm 3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 rm@tstreet.com 5 Leonard Hofstadter Male NULL Woodcrest NULL 845738767 NULL 6 Sheldon Cooper Male NULL Woodcrest NULL 976736763 NULL 7 Rajesh Koothrappali Male NULL Woodcrest NULL 938867763 NULL 8 Leslie Winkle Male 14-02-1984 Woodcrest NULL 987636553 NULL 9 Howard Wolowitz Male 24-08-1981 SouthPark P.O. Box 4563 987786553 lwolowitz[at]email.me
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 operations involving NULL can either return true or false. Using other comparison operators returns "Unknown"(NULL).
Suppose you compare number five with 5
SELECT 5 =5;
The query result is 1 which means TRUE
| 5 =5 |
|---|
| 1 |
Let's do the same operation with NULL
SELECT NULL = NULL;
| NULL = NULL |
|---|
| NULL |
Let's look at another example
SELECT 5 > 5;
| 5 > 5 |
|---|
| 0 |
The query result is 0 which means FALSE
Let's look at same example using NULL
SELECT NULL > NULL;
| NULL > NULL |
|---|
| NULL |
Lets use the IS NULL keyword
SELECT 5 IS NULL;
| 5 IS NULL |
|---|
| 0 |
The query result is 0 which is FALSE
SELECT NULL IS NULL;
| NULL IS NULL |
|---|
| 1 |
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.
.jpg)
0 comments:
Post a Comment