Introduction to Advanced SQL for Professional Developers

Bulk Insert and Export Data with csv Files with Postgres copy Command

On conflict Do Something Clause in Postgres

Casting Types in SQL

Create a Custom SQL Enum Type 

Profiling Queries with SQL Explain Analyze

Common Table Expressions in SQL

Conditional Returns with SQL case / then

Create Variables and Blocks with Do / Declare in SQL

Perform Multiple Steps in One with SQL Transactions

Filter Grouped and Aggregated Data with SQL Having

Create and Safely Handle Nulls in SQL

Aggregate Inline filters in SQL

Work with JSON Data in SQL

Pattern Matching and Regular Expressions in SQL

Advanced SQL for Professional Developers

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. 


https://app.egghead.io/api/v1/lessons/postgresql-create-and-safely-handle-nulls-in-sql/subtitles

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.

Create and Safely Handle Nulls in SQL

Share this video with your friends

Course

Advanced SQL for Professional Developers