Copyright

SQL: ISNULL Function

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

Not every field in a database has a value: Sometimes we want to show something other than NULL or empty. The ISNULL function in SQL is perfect for these situations. This lesson will cover the syntax and provide examples of the function.

Dealing With NULL Values

Since there will often be situations where we run across an empty or NULL value in a table, SQL provides the ISNULL statement. This tool lets us display something else or perform another task when we hit the NULL value.

NOTE: The ISNULL function is used in SQL Server to replace a NULL value with another value. The same function in MySQL returns a 0 if the value is not null, or a 1 if it is. Further processing is required. Therefore, we will keep our focus on SQL Server and how to use its power to replace a NULL value with a different one.


ISNULL(expression, alternate_value)


The expression is the value or field we are looking at; the alternate_value is the value we will use if the field or value is NULL.

Examples

One use for ISNULL is for SQL statements that perform mathematical calculations: It is not possible to do multiplication on a field value if it is NULL. It isn't zero, but empty. Therefore, using the ISNULL statement will let us set any null fields to 0. This works for multiplication, addition, or averages.

Total Cost of a Product (Multiplication)

Here is an example where we calculate the cost of items in the inventory. If we don't have the item on hand, the value is NULL; to make the query run correctly, we'll replace that NULL with a zero:


SELECT itemName, cost * ISNULL(qty, 0)
FROM tblInventory;


Addition

Let's say we have a table that stores payroll data by quarter and want to add up some amounts for pay codes. Some pay codes may never be used in a given quarter, so we have to make sure we account for the NULL values:


SELECT q1.payCode, q2.payCode
  ISNULL(q1.waeAmount, 0) +
  ISNULL(q2.wageAMount, 0)
FROM tblQuarterlyWage;


The totals are summed, replacing any NULL values with 0. Remember, if we had tried to add the NULL value, the system would have returned an error; therefore the ISNULL let's us still complete the mathematical function by filling in a zero instead of the nasty NULL value.

Average

The next example makes use of the AVG function in SQL, and figures out the average of ratings for a table of music albums. If any rating is NULL, we replace it with 50, which would indicate the median value (or may indicate unrated).


SELECT AVG(ISNULL(rating, 50))
FROM tblAlbum;


To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support