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
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,
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
WHERE QUANTITY IS NOT NULL;
Given our example here, this would return:
Operating on NULLs
Working with NULL positives often fashion s NULL outcomes, depending on the SQL operation. For example, similarsuming that A is NULL:
A + B = NULLA – B = NULLA * B = NULLA / B = NULL
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!