{"id":12155,"date":"2022-03-29T11:34:07","date_gmt":"2022-03-29T09:34:07","guid":{"rendered":"https:\/\/careerfoundry.com\/en\/?p=12155"},"modified":"2022-09-29T15:20:09","modified_gmt":"2022-09-29T13:20:09","slug":"isnull-sql","status":"publish","type":"post","link":"https:\/\/careerfoundry.com\/en\/blog\/data-analytics\/isnull-sql\/","title":{"rendered":"Exploring the ISNULL() SQL Function"},"content":{"rendered":"
I\u2019ve been working with data professionally for almost a decade, and the first thing I tell all newcomers is to start mastering relational databases and Structured Query Language (SQL). Understanding how to query and manipulate data in databases is an important skill because every company has data and most want to understand it. Learning the basics of SQL is easy, and the skill can land a high-paying job if you\u2019re good at it.\u00a0<\/span><\/p>\n Whether you\u2019re learning SQL to become a database administrator, data analyst or data scientist, it is important to understand how to handle NULL values. In this tutorial we\u2019re going to cover how to use the ISNULL() function in <\/span>SQL Server<\/span><\/a>, as well as some tips and tricks to make sure you\u2019re applying ISNULL() correctly.<\/span><\/p>\n You can use this clickable menu to skip ahead to any section you’re interested in:<\/p>\n Before we explore the ISNULL() function, we need some data to play with. I spun up a little database named <\/span>Test <\/b>and created a table named <\/span>people <\/span><\/i>containing six columns. Then I inserted three records into the table. Use this script to replicate the people table and example records:<\/span><\/p>\n use Test<\/span><\/p>\n CREATE TABLE people<\/span><\/p>\n (personID\u00a0 INT IDENTITY(1, 1) NOT NULL,<\/span><\/p>\n firstName VARCHAR(50) NOT NULL,<\/span><\/p>\n homePhone VARCHAR(15) NULL,<\/span><\/p>\n workPhone VARCHAR(15) NULL,<\/span><\/p>\n cellPhone VARCHAR(15) NULL,<\/span><\/p>\n age INT NULL<\/span><\/p>\n );<\/span><\/p>\n insert into people(firstname, homephone, workphone, cellPhone, age)<\/span><\/p>\n values(‘Eric’, ‘555-555-6655’, ‘555-555-1234’, null, 34);<\/span><\/p>\n insert into people(firstname, homephone, workphone, cellPhone, age)<\/span><\/p>\n values(‘Tom’, null, ‘555-555-4457’, null, 43);<\/span><\/p>\n insert into people(firstname, homephone, workphone, cellPhone, age)<\/span><\/p>\n values(‘Lucy’, ‘555-555-7978’, null, ‘555-555-1212’, null);<\/span><\/p>\n As we can see, the table contains six fields, four of which can contain null values:<\/span><\/p>\n We can review the inserted records using a SELECT statement:<\/span><\/p>\n SELECT * FROM people<\/span><\/p>\n <\/p>\n Notice the three records display NULL for the phone numbers and age that do not exist. Although it says NULL in the results, it is important to understand data is absent from that field. Null is the absence of data, meaning the NULL values are completely empty.\u00a0<\/span><\/p>\n Although many consider ISNULL() to be an advanced SQL function, it is very easy to understand and use. Before getting into examples, let’s take a look at the syntax for the ISNULL() function:<\/span><\/p>\n ISNULL(expression, value)<\/span><\/p>\n The <\/span>Expression <\/b>corresponds to the field that will be<\/span> checked by the function to see if it\u2019s NULL or not. The <\/span>Value <\/b>corresponds to the specified value that will be returned if the <\/span>expression<\/span><\/i> is NULL.<\/span><\/p>\n When querying data and using ISNULL(), the function returns a specified <\/span>value <\/span><\/i>if the <\/span>expression <\/span><\/i>is found to be NULL. If the expression is not NULL, then the expression value is returned. For example, if we look at the cellPhone column in the example data, we see two of the three records show NULL values. If cellPhone is passed into ISNULL() as the <\/span>expression<\/b> and the word \u201cN\/A\u201d is set as the <\/span>value<\/b>, the function will return the specified value, \u201cN\/A\u201d for two of the three records.<\/span><\/p>\n The ISNULL() function can be used in multiple situations. The most straightforward way is to use it whenever you want to output a value if a NULL is found. Using the example data, let\u2019s write a SELECT statement that will return <\/span>firstName <\/b>and <\/span>homePhone <\/b>fields. Using ISNULL(), we can make the query return the text, \u201cNo Home Phone\u201d if the homePhone field is NULL:<\/span><\/p>\n select firstName,<\/span><\/p>\n ISNULL(homePhone, ‘No Home Phone’) as HomePhoneNumber<\/span><\/p>\n from people<\/span><\/p>\n <\/p>\n We can see in the results, Tom returns the text<\/span> No Home Phone<\/b> since his homePhone value is NULL in the table.\u00a0<\/span><\/p>\n Another way to use the ISNULL() function is to have it return a different column in the data, like a different phone number, instead of the \u201cNo Home Phone\u201d<\/span> text. For example, this expression returns the workPhone value if the homePhone is NULL:<\/span><\/p>\n select firstName, ISNULL(homePhone, workPhone) as HomeOrWork<\/span><\/p>\n from people<\/span><\/p>\n <\/p>\n Notice Tom\u2019s workPhone value now outputs because it was specified in the function and the ISNULL <\/span>expression<\/b> is finding a NULL value for his homePhone.\u00a0<\/span><\/p>\n The ISNULL() function can also be used in dynamic SQL or when using declared variables. For example, declared variables can be passed into the function just like we did with column names in the previous example\u2019s SELECT statement.<\/span><\/p>\n DECLARE @expression VARCHAR(50);<\/span><\/p>\n DECLARE @value VARCHAR(50);<\/span><\/p>\n SET @expression = null;<\/span><\/p>\n SET @value = ‘This tutorial is great!’;<\/span><\/p>\n SELECT ISNULL(@expression, @value) AS example;<\/span><\/p>\n <\/p>\n Notice the declared <\/span>value<\/b> returned since the declared <\/span>expression <\/span><\/i>was set to NULL.\u00a0<\/span><\/p>\n It is also possible to use ISNULL() within aggregate functions like AVG() and SUM(). Let\u2019s say we want the average age of the people in the dataset. We can use ISNULL() to supply a value to the nulls as a means for estimating the average age:<\/span><\/p>\n select avg(ISNULL(age, 30)) as averageAge from people<\/span><\/p>\n <\/p>\n While ISNULL() is a powerful function, there are some situations where it is not the best function for the job. Consider we have three phone number fields in the data. If we want to look at all three fields and return the first non-null value, the COALESCE() function should be used instead of the ISNULL() function.<\/span><\/p>\n The COALESCE() function is used to return the first non-null value in a given list. For example, let\u2019s say we want to query all three phone number fields, but only want one value returned. We decide we want to first check the cellPhone field. If the cellPhone value doesn\u2019t exist, we want the homePhone value. If homePhone doesn\u2019t exist, we want workPhone. Using COALESCE(), we can pass that list of phone number columns into the query and get a single non-null phone number back.<\/span><\/p>\n Here is an example of using COALESCE():<\/span><\/p>\n select firstName, COALESCE(cellPhone, homePhone, workPhone) as phoneNumber<\/span><\/p>\n from people<\/span><\/p>\n <\/p>\n The ISNULL() function is limited to the two parameters, but the COALESCE() function can take a list of many parameters. The COALESCE() functionality can be replicated using the ISNULL() function, but we need to nest ISNULL() within ISNULL(), making the code harder to understand versus using COALESCE(). Nesting the ISNULL function inside itself to get the same results as COALESCE() would look like this:<\/span><\/p>\n select firstname, ISNULL(cellphone, ISNULL(homePhone, workPhone)) from people<\/span><\/p>\n <\/p>\n Notice the output is the same whether we use COALESCE() or nested ISNULL() functions. As you can imagine, if you have many items in your list, nesting ISNULL() would become very cumbersome. While it works, coalescing the data is better because it makes the code easier to read.\u00a0<\/span><\/p>\n Another time ISNULL() will not work is if the data types are not compatible. In SQL Server, the ISNULL() function tries to convert the lower precedence data types into a higher precedence data types. If ISNULL() is not able to convert the data type, it returns an error message.<\/span><\/p>\n For example, if we use ISNULL() on personID an try to return the current datetime on a null value, an error will display:<\/span><\/p>\n select ISNULL(personID, getdate()) from people<\/span><\/p>\n <\/p>\n As the error message explains, converting the datetime to an integer is not possible.<\/span> Refer to the official documentation for more information on data types and conversions.<\/span><\/a><\/p>\n Dealing with NULL values in SQL Server can be tricky, especially when preparing data for things like machine learning. Using built-in SQL Server functions like ISNULL() and COALESCE() give you options to work around NULL values in the data. In this article we not only explored the situations in which ISNULL() can be used to replace NULL values with a specified value, but we also covered a few situations in which it is not the correct function for the job.<\/span><\/p>\n Want to get a more in-depth look at SQL through a formal course? We’ve rounded up a list of the best SQL certifications<\/a> in this post.<\/p>\n To learn more about data analytics, try out this <\/span>free 5-day short course<\/span><\/a>, or check out some of our other data analytics articles:<\/span><\/p>\n Whether you\u2019re learning SQL to become a database administrator, data analyst or data scientist, it is important to understand how to handle NULL values. In this tutorial we\u2019re going to cover how to use the ISNULL() SQL function.<\/p>\n","protected":false},"author":133,"featured_media":12157,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_lmt_disableupdate":"no","_lmt_disable":"","footnotes":""},"categories":[3],"tags":[],"acf":{"homepage_category_featured":false},"modified_by":"Kirstie Sequitin","_links":{"self":[{"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/posts\/12155"}],"collection":[{"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/users\/133"}],"replies":[{"embeddable":true,"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/comments?post=12155"}],"version-history":[{"count":0,"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/posts\/12155\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/media\/12157"}],"wp:attachment":[{"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/media?parent=12155"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/categories?post=12155"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/careerfoundry.com\/en\/wp-json\/wp\/v2\/tags?post=12155"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
1. Before we get started: creating test data<\/span><\/h2>\n
\n
2. What is the ISNULL() function?<\/span><\/h2>\n
3. Using the ISNULL() function<\/span><\/h2>\n
4. When not to use the ISNULL() function<\/span><\/h2>\n
COALESCE versus ISNULL<\/span><\/h3>\n
5. Data type conversion with ISNULL<\/span><\/h2>\n
6. Wrapping up<\/span><\/h2>\n
\n