Click here to Skip to main content
13,139,569 members (55,031 online)
Click here to Skip to main content
Add your own
alternative version

Stats

4.9K views
5 bookmarked
Posted 23 Oct 2014
MIT

Get Ready to Learn SQL Server: What is a Database NULL Value?

, 23 Oct 2014
Rate this:
Please Sign up or sign in to vote.
What is a Null Value? In databases a common issue is what value or placeholder do you use to represent a missing values.   In SQL, this is solved with null.  It is used to signify missing or unknown values.  The keyword NULL is used to indicate these values.

What is a Null Value?

In databases a common issue is what value or placeholder do you use to represent a missing values.   In SQL, this is solved with null.  It is used to signify missing or unknown values.  The keyword NULL is used to indicate these values.  NULL really isn’t a specific value as much as it is an indicator.  Don’t think of NULL as similar to zero or blank, it isn’t the same.  Zero (0) and blanks “ “, are values.

In most of our beginning lessons we’ve assumed all tables contained data; however, SQL treats missing values differently.  It is important to understand how missing values are used and their effect on queries and calculations.

Database Tables and Values

In many situations every row and column will contain data, but there cases where it makes sense for some columns to not contain a value.

Customer 

CustomerIDNameCityStateAgeSex
2Bob PetersonNew YorkNY55M
3Sue LinkleOmahaNE F
4ChrisGreenMI  
5Lori OttermanOslo   
6Acme PlumbingAustinTX  

 

Consider the above Customer table.  Several columns contain missing or unknown values.  Reasons why this can happen include a value is:

  • missing – Perhaps a customer, such as Sue, doesn’t divulge her age to your customer service representative.
  • unknown – An employee’s termination date is usually some event in the unforeseen future.
  • doesn’t apply – If the customer is a business, then Sex doesn’t apply.

You could argue that for text values you could use blanks, such as one space ‘ ‘, or even an empty value, which is two single quotes ‘’ to represent a missing value.  Yet this strategy doesn’t work well for numbers or dates.  If the customer’s age is unknown, what numeric value would you use?  Would it make sense to use zero or a negative number?

I think doing that causes more confusion and would make it very easy to skew results.  For instance, if you were going to calculate the average age of your female customers, the zeros, the ones you were using to represent missing values, would cause the actual average age to be lower than you would expect.

SQL reserves the NULL keyword to denote an unknown or missing value.  In SQL NULL’s are treated differently than values.  Special consideration is required when comparing null value or using them in expressions.

NULL Value in Comparisons:

When it isn’t possible to specially code your data using “N/A” you can use the special keyword NULL to denote a missing value.  NULL is tricky.  NULL isn’t a value in the normal sense.  For instance no two NULL are equal to each other.  Surprisingly NULL = NULL is FALSE!

SQL covers this dilemma though.  You can use the IS NULL and IS NOT NULL comparisons to test for NULL values.

For example, the following query from the AdventureWorks2012 database

SELECT ProductID,
       Name,
       Color
  FROM Production.Product
 WHERE Color IS NULL

will return all products whose color is missing; whereas

SELECT ProductID,
       Name,
       Color
  FROM Production.Product
 WHERE Color IS NOT NULL

will return all products having a color value.

NULLS in Expressions

As you can expect nulls have an adverse effect in expressions.  Since NULL denotes an undefined value, its participation in most expressions renders the expression unknown, or NULL, as well.

Check out the following arithmetic expression.  The first line evaluates to a number the others to NULL:

  • 20 + (5 * 4) = 20 + 20 = 40
  • NULL + (5 * 4) = NULL + 20 = NULL
  • 20 + (NULL * 4) = 20 + NULL = NULL

NULL has the same effect on text. Expressions:

  • ‘Happy’ + ‘ ‘ + ‘Holidays’ = ‘Happy Holidays’
  • NULL + ‘ ‘ + ‘Holidays’ = NULL

Try running the following query in the AdventureWorks2012 database:

SELECT ProductID,
       Color + ' ' +Name
  FROM Production.Product

Did you notice you either get value like “Black Chainring” or NULL?  You may expect to see just the product‘s name if the color is missing, but since the color is NULL, the expression is also NULL.

Without help, it would be tough to create expressions, as the NULL’s would “ruin” most results!

In this case you can use the COALESCE function to assist.  This function will return the first non-NULL value from a list of parameters.  For example

COALESCE(NULL, 'A', 'B')

Returns ‘A’, since it is the first non-NULL value found.

We can use COALESCE to replace a NULL with another value.  In this way we can then continue to build an expression that will return a result free of NULL value.

Our improved SQL statement is:

SELECT ProductID,
       COALESCE(Color + ' ','') + Name
FROM   Production.Product

How does the work?  I’ve highlighted some important bits in the statement:

  • If color is the value “Black,” then the COALESCE function will return “Black “ with the training space.  The product name is then appended to form the full value.
  •  If the color is NULL, then COALESCE seeks out the first non-null value.  This happens to be the empty string ”.  Using this trick, it makes it really easy to return either the combination of two columns, or just the second column.

NULL Values in Where Clauses

Where clauses are used to limit the rows returned from a query.  Generally only rows meeting the where clause are returned.  Rows whose where clause evaluates to FALSE are removed from the result.

In similar fashion if the where clause evaluates to NULL, the row is eliminated.

SELECT ProductID,
       Name,
       Color
FROM   Production.Product
WHERE  LEN(COLOR) < 100

Will return all rows where a color is specified.  LEN is a function that returns the number of characters in a value; LEN(‘Black’) returns 5.  Since this is less than 100 it would be included in the result.

In cases where COLOR is NULL, then LEN(COLOR) returns NULL.  Since NULL < 100 is always false, the row is removed from the result.

NULL in Boolean Expressions (Intermediate)

Since NULL represents a missing or unknown value, its effect on Boolean expressions is curious.  It status as an unknown value puts a spin on the outcome.

I think the results for AND are what you would expect, but the results you would expect for OR are not.  In many cases when a value is unknown, the uncertainty of the value translates through the expression.

Boolean AND Operator

The AND operator returns a TRUE only if all conditions are also TRUE.  The following truth table shows all combinations of values the condition (A AND B).  Keep in mind we use NULL to denote unknown values.

Condition ACondition BResult
TRUEFALSEFALSE
TRUETRUETRUE
TRUENULLNULL
FALSEFALSEFALSE
FALSETRUEFALSE
FALSENULLNULL
NULLFALSENULL
NULLTRUENULL
NULLNULLNULL

The one of the inputs being unknown, there is no possibility the expression can be TRUE, as we can’t be certain both inputs are TRUE.  Conversely, we don’t know whether the unknown value is FALSE.  This leaves to concluding the result, in general is unknown.

Boolean OR Operator

The OR operator returns a TRUE when one or more conditions are also TRUE.  Here is the Truth table for the OR operator.

Condition ACondition BResult
TRUEFALSETRUE
TRUETRUETRUE
TRUENULLTRUE
FALSEFALSEFALSE
FALSETRUETRUE
FALSENULLNULL
NULLFALSENULL
NULLTRUETRUE
NULLNULLNULL

You’ll see that in every case one of the conditions is true, so is the end result.  This is even the case when the other input is NULL.  All that matters is one input is TRUE, the other input, albeit unknown, is irrelevant.

If you have other questions you want answered, then post a comment or tweet me.  I’m here to help you. What other topics would you like to know more about?

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionThe NULL state Pin
pstjean24-Oct-14 2:26
memberpstjean24-Oct-14 2:26 
AnswerRe: The NULL state Pin
essentialSQL1-Nov-14 0:35
memberessentialSQL1-Nov-14 0:35 
GeneralRe: The NULL state Pin
pstjean1-Nov-14 5:59
memberpstjean1-Nov-14 5:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170915.1 | Last Updated 24 Oct 2014
Article Copyright 2014 by essentialSQL
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid