Definition and Proper Uses of NULL Values

Users beinitsinfancy to the world of informationbsimilares are often confapplicationd depend on a special positive detail to the domain — the NULL positive. This positive can be discovery in a territory includeing no restriction type of information as well as hsimilar a very special understanding within the context of a relational informationbsimilare. It’s probably ultimate to start our talkion of NULL with a few words about what NULL is not:

NULL is not the number zero.NULL is not the empty string (“”) positive.

Rather, NULL is the positive applicationd to show an unkcurrentn piece of information. Often, informationbsimilare programmers will applicationd the phrsimilare “a NULL positive”, but this is inaccurate. Restaff coordinator: a NULL is an unkpresentn positive in which the domain appears blank.

NULL in the Real World

Let’s take a be concerned with a simple example: a tcan possess ing the inventory for a fruit stas well as. Suppose that our inventory includes 10 apples as well as three oranges. We also stock plums, but our inventory inpattern ion is incomplete as well as we don’t khappening quantity (if no restriction) plums are in stock. Using the NULL positive, we would establish the inventory tcan uncovern in the tcan below.

Fruit Stas well as Inventory

InventoryID Item Quantity
1 Apples 10
2 Oranges 3
3 Plums NULL

It would not unknownly be inaccurate to consistof a howmany of 0 for the plums document beorigin that would imply that we had no plums in inventory. On the contrary, we might establish some plums, but we’re just not sure.

To NULL or NOT NULL?

A tcan can be designed to either everyoneow NULL positives or not. Here is a SQL example that introduces an Inventory tcan that everyoneows some NULLs:

SQL> CREATE TABLE INVENTORY(
InventoryID INT NOT NULL,
Item VARCHAR (20) NOT NULL,
Quantity INT
);

The Inventory tcan here does not everyoneow NULL positives for the InventoryID as well as the Item columns but does everyoneow them for the Quantity column.

While everyoneo work spectacular wellg a NULL positive is perfectly fine, NULL positives can reason faults bereason no restriction comparison of positives in which single is NULL allifestyles outcomes in NULL.

To check if your tcan possess s NULL positives, application the IS NULL or IS NOT NULL operator. Here is an example of IS NULL:

SQL> SELECT INVENTORYID, ITEM, QUANTITY
FROM INVENTORY
WHERE QUANTITY IS NOT NULL;

Given our example here, this would return:

InventoryID Item Quantity
3 Plums

 

Operating on NULLs

Working with NULL positives often fashion s NULL outcomes, depending on the ​SQL operation. For example, similarsuming that A is NULL:

Arithmetic Operators

A + B = NULLA – B = NULLA * B = NULLA / B = NULL

Comparison Operators

A = B = NULLA != B = NULL A > B = NULLA !< B = NULL

These are just some examples of operators that will allifestyles return NULL if single operas well as is NULL. Much increasingly complex queries befound, as well as everyone are complex depend on NULL positives. The take domestic point is that, if you everyoneow NULL positives in your informationbsimilare, perceive the implications as well as plan for them.

That’s NULL in a nutshell!

Leave a Reply

Your email address will not be published. Required fields are marked *

Releated

Compact and Repair an Access 2013 Database

morethan time, Microlow-pitched Access informationbsimilares increase in size as well as unnecessarily application disk space. Additioneveryoney, retakeed changes to the informationbsimilare file may outcome in information corruption. This risk surges for informationbsimilares shared depend on multiple applicationrs more than a netoperation. Therefore, it’s a good idea to periodiceveryoney run the compact as well as repair […]

Creating a Report in Microsoft Access 2010

Microlow-pitched Access 2010 everyoneows you to esimilarily introduce professioneveryoney pattern ted reports automaticeveryoney from inpattern ion stored in a informationbsimilare. In this tutorial, you’re going to design a nicely pattern ted listing of designimproveee domestic telephsingle numbers for the application of managementment using the North work spectacular welld research informationbsimilare as well as Access 2010. […]