Create and Safely Handle Nulls in SQL

Tyler Clark
InstructorTyler Clark
Share this video with your friends

Social Share Links

Send Tweet
Published 4 years ago
Updated 3 years ago

Nulls can be a huge burden if not handled correctly. Conditional logic is tricky and joining tables with null columns requires cognitive recognition. Similar to many popular programming languages, we can conditionally change values within SQL if the returning value is null.

Instructor: [0:00] Let's create a table called posts. This table is going to hold some blog posts of ours to be viewed later. As you can see, we're going to have an id, a title, an excerpt, a body. Then, let's add some dates like created_at and updated_at. Let's enter some data into our posts table, and then, let's select and see what we've got to work with here.

[0:24] We have three blog posts. They all have titles. One has an excerpt with text that we can read. This one is not null, it's an empty text and you can see that on this line here where we're inserting it into with an empty string. This last one is null, as you can see here as well. They all have body and a created_at date.

[0:45] Our goal is to display a post overview page that's going to show the title and the excerpt of each post. In the case that the excerpt is not provided, we default to the first 40 characters of the post body.

[0:57] No one likes to deal with nulls. When pulling out this post, let's COALESCE the nulls into the first characters of the body. The COALESCE function accepts an unlimited number of arguments. It returns the first argument that's not null. If they're all null, then it will return null.

[1:14] The LEFT function returns the first in [inaudible] characters from a string. With that in mind, looking at our results, you can see that because test post three had a null excerpt, we return the first 40 characters of the body. However, empty string did not return those characters of the body.

[1:31] In order to solve this, we can force empty strings to be nulls by using the NULLIF function. Then our COALESCE will return the body characters now that we have a null here. As you can imagine, the NULLIF function returns a null value if argument one is equal to argument two. Otherwise, it will return argument one.

[1:55] Because our post two had an empty string, this matched here within a NULLIF. This is going to be turned a null which makes the COALESCE return the first 40 characters of the body for our test post two.

egghead
egghead
~ 11 minutes ago

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on egghead.io

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at support@egghead.io.

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today