{"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

    \n
  1. Before we get started: creating test data<\/a><\/li>\n
  2. What is the ISNULL function?<\/a><\/li>\n
  3. Using the ISNULL function<\/a><\/li>\n
  4. When not to use the ISNULL function<\/a><\/li>\n
  5. Data type conversion with ISNULL<\/a><\/li>\n
  6. Wrapping up<\/a><\/li>\n<\/ol>\n

    1. Before we get started: creating test data<\/span><\/h2>\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