How To Software What You Should Know About NULL Values Understand the Use of NULLs to Avoid Database Problems Share Pin Email Print Microsoft Software Databases Documents Spreadsheets Presentations Desktop Publishing Graphic Design Animation & Video by Mike Chapple An IT professional with more than 10 years of experience in the fields of databases and cybersecurity. Updated May 29, 2019 Users new to the world of databases are often confused by a special value particular to the field — the NULL value. This value can be found in a field containing any type of data and has a very special meaning within the context of a relational database. It’s probably best to begin our discussion of NULL with a few words about what NULL is not: NULL is not the number zero.NULL is not the empty string (“”) value. Rather, NULL is the value used to represent an unknown piece of data. Often, database programmers will used the phrase "a NULL value", but this is incorrect. Remember: a NULL is an unknown value in which the field appears blank. NULL in the Real World Let’s take a look at a simple example: a table containing the inventory for a fruit stand. Suppose that our inventory contains 10 apples and three oranges. We also stock plums, but our inventory information is incomplete and we don’t know how many (if any) plums are in stock. Using the NULL value, we would have the inventory table shown in the table below. Fruit Stand Inventory InventoryID Item Quantity 1 Apples 10 2 Oranges 3 3 Plums NULL It would clearly be incorrect to include a quantity of 0 for the plums record because that would imply that we had no plums in inventory. On the contrary, we might have some plums, but we’re just not sure. To NULL or NOT NULL? A table can be designed to either allow NULL values or not. Here is a SQL example that creates an Inventory table that allows some NULLs: SQL> CREATE TABLE INVENTORY( InventoryID INT NOT NULL, Item VARCHAR (20) NOT NULL, Quantity INT ); The Inventory table here does not allow NULL values for the InventoryID and the Item columns but does allow them for the Quantity column. While allowing a NULL value is perfectly fine, NULL values can cause problems because any comparison of values in which one is NULL always results in NULL. To check if your table contains NULL values, use 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 values often produces NULL results, depending on the SQL operation. For example, assuming 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 always return NULL if one operand is NULL. Much more complex queries exist, and all are complicated by NULL values. The take home point is that, if you allow NULL values in your database, understand the implications and plan for them. That's NULL in a nutshell! Continue Reading 5 Things Beginners Need to Know About Databases The Fundamental Guide to SQL How a Database Domain Protects your Data's Integrity Learn About NOT NULL Constraints in Microsoft SQL Server SQL FAQ: Get Answers to Common Structured Query Language Questions How to Use the IRR Formula in Excel An Easy Guide to Creating Databases and Tables in SQL A Database Relation Might Not Be What You Think Database Terms You Need to Know Linux / Unix Command: ash A Quick Guide to Understanding Database Dependencies Designing a Database? Give Your Tables Attributes to Be Proud Of What Is a UNIQUE Constraint in a SQL Server? Learn to Manage Data in Excel With Databases, Tables, and Fields The Definition of "Value" in Excel Has Changed in Interesting Ways Is Plum a Perfect Color for Romance?