NULL HANDLING

Understand how to handle NULL values

15 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Null Functions Overview — Null functions are used to perform operations specifically on null values stored in database tables.
Defining NULL — NULL is a special value representing unknown or missing data, distinct from zero or blank strings.
IS NULL Function — IS NULL returns a boolean value indicating if an expression is null, often represented as 1 or 0.
COALESCE Function — COALESCE returns the first non-null expression from a list of arguments provided to it.
NULLIF Function — NULLIF compares two expressions and returns NULL if they are equal, otherwise it returns the first expression.
IFNULL Function — IFNULL replaces a null value in the first argument with the specified value in the second argument.
Database Compatibility — Always check the specific database documentation to ensure a null function is supported before implementation.

PDF notes

Bottom line: You will be able to handle missing or unknown data effectively using specialized SQL NULL functions. You can replace NULL values with defaults, conditionally return values based on equality, or check for the presence of NULLs in expressions.

This lesson on NULL HANDLING is hands-on and example-driven. You will be able to handle missing or unknown data effectively using specialized SQL NULL functions. You can replace NULL values with defaults, conditionally return values based on equality, or check for the presence of NULLs in expressions.

What You'll Be Able To Do

Distinguish between NULL, zero, and blank values in a database context.
Write queries that return a boolean indicator for NULL values.
Use COALESCE to select the first available non-NULL value from a list of columns.
Apply IFNULL to substitute a specific default value when data is missing.
Construct a NULLIF statement to return NULL when two expressions match.

Topics Covered in NULL HANDLING

Null Functions Overview (0:00 - 1:00) — Null functions are used to perform operations specifically on null values stored in database tables.
Defining NULL (1:00 - 3:00) — NULL is a special value representing unknown or missing data, distinct from zero or blank strings.
IS NULL Function (3:00 - 6:00) — IS NULL returns a boolean value indicating if an expression is null, often represented as 1 or 0.
COALESCE Function (6:00 - 9:00) — COALESCE returns the first non-null expression from a list of arguments provided to it.
NULLIF Function (9:00 - 12:00) — NULLIF compares two expressions and returns NULL if they are equal, otherwise it returns the first expression.
IFNULL Function (12:00 - 14:00) — IFNULL replaces a null value in the first argument with the specified value in the second argument.
Database Compatibility (14:00 - 14:40) — Always check the specific database documentation to ensure a null function is supported before implementation.

SQL Cheat Sheet

NULL — Special value representing unknown, missing, or not applicable data
```
SELECT * FROM employee WHERE salary IS NULL;
```
IS NULL(expression) — Returns 1 (True) if the expression is NULL, 0 (False) otherwise
```
SELECT IS NULL(salary) AS null_check FROM employee;
```
COALESCE(e1, e2, ...) — Returns the first non-NULL expression in the provided list
```
SELECT COALESCE(salary, ID) AS result FROM employee;
```
NULLIF(e1, e2) — Returns NULL if e1 equals e2; otherwise returns e1
```
SELECT NULLIF(ID, new_ID) AS result FROM employee;
```
IFNULL(e1, e2) — Returns e2 if e1 is NULL; otherwise returns e1
```
SELECT IFNULL(salary, 999) AS result FROM employee;
```

Comparison Table

Function	Primary Purpose	Arguments Required
COALESCE	Return first non-NULL value	Two or more expressions
IFNULL	Replace NULL with a default	Exactly two expressions
NULLIF	Return NULL if arguments match	Exactly two expressions

Common Pitfalls

Mistake: Assuming NULL is the same as zero or a blank string. Avoid: Always use IS NULL or specific NULL functions for checks.
Mistake: Expecting NULLIF(A, B) to return B when A=B. Avoid: NULLIF always returns NULL if the two arguments match.
Mistake: Using NULL functions without checking database support. Avoid: Verify if your specific DBMS supports IFNULL or similar functions.

FAQs

Is NULL the same as zero or a blank string? No, NULL is a special value indicating missing or unknown data. It is neither zero nor blank.
How can I treat NULL as zero during aggregation? Use a function like IFNULL(column, 0) or COALESCE(column, 0) inside the aggregate function, such as SUM().
What happens if all arguments in COALESCE are NULL? If all expressions passed to COALESCE are NULL, the function itself will return NULL.
If NULLIF(A, B) returns NULL, how do I get B instead? You must swap the parameters: NULLIF(B, A) will return B if A and B are different.

Common pitfalls

Mistake: Assuming NULL is the same as zero or a blank string. Avoid: Always use IS NULL or specific NULL functions for checks.
Mistake: Expecting NULLIF(A, B) to return B when A=B. Avoid: NULLIF always returns NULL if the two arguments match.
Mistake: Using NULL functions without checking database support. Avoid: Verify if your specific DBMS supports IFNULL or similar functions.

Hello everyone. Welcome back. In this presentation, let's focus on null functions in SQL. We have seen about null values elaborately in chapter two of this playlist. In this presentation, what we are going to focus is null functions. At first, we need to understand why do we need null functions in SQL. Before understanding what is null functions, I would like to reiterate few things about null values, because these null functions are going to be operated on null values. We know null is a special value in DBMS. When we talk about database management system, due to the advancements of databases over the years, databases are enriched to store any type of data, not just numbers or text or decimals. Even we have multimedia databases that can store multimedia data as well. One different type of value it is going to store is a null value. The null value is a special value in database management system. Why we are referring it as a special value is that because this null represents the value of the attributes that are unknown or not applicable or missing for that particular row. In databases when we have a situation that the value is not known or if it is not applicable or if it is missing, in those situations null values are really helpful. If you want more information about null values, I would request you to navigate to chapter two of this playlist and kindly visit null values lecture once to gain better understanding about null values. So, obviously, null is neither blank nor zero. This is a special value. It is neither blank nor zero. And that is why we refer this value as a special value. The topic of this presentation is null functions and these null functions actually operates on null values and that's why this point reveals the fact that null functions are used to perform operations on null values that are stored in the database tables. So, from this it's clear that we have an exclusive function that can operate on null values that are stored in the databases. And these functions are referred as null functions. We are done with the basics of null functions. Let's now see what are the various null functions in SQL. In this presentation, I'm going to explain about four null functions. The first function is is null, the second one is coalesce, the third function is nullif, and the last function is ifnull. At first, let's start with is null, the null function. When we are talking about is null null function, the idea is so simple. The name itself says that is null, meaning is it null? And the answer is going to be either yes or no. In language perspective, the option is going to be a boolean value. It returns true if it is a null, it returns false if it is not null. The working of is null is straightforward. Let's see an example. For that, let's take a table, which is the employee table here, where we have attributes such as ID, first name, last name, department, and salary. For some reasons, there are null values for some records in the database or in the table. Now, let's see how this null function is going to operate on this table. Let's see example number one, which is select one of the attribute or the column in the database in the table, and the second column or attribute I want to retrieve is not the direct attribute as mentioned in the table, whereas this is is null function. And I'm providing an argument to this is null function, which is the salary attribute, as null_val from employee. So, we have an employee table and we're going to retrieve salary from this table. Also, we are going to have another column, which is going to contain the output of is null function for the salary value, and the name of the new column that we are retrieving is null_val. Let's see the output now. So, the output is going to contain two columns, salary and null_val. So, the output is going to contain salary and null_val. As I mentioned, this is null is going to return boolean value. Zero means if it is not null, one means if it is null. If you observe, for this 50,000, which is actually the salary, is 50,000 a null value? No, this is not a null value and that is why is null is returning zero here. Talking about this, no null value, this is also not a null value, this is also not a null value. Wherever we have null value in the salary column of the table, there the value returned is one, because this is a null value. I hope example number one is clear for you. Example number one is all about is null function. Let's move on to example number two with a small variation in the usage of is null function. Let's see that now. In example number two, obviously, we are going to use the same table, and the query is select sum is null salary, 10,000 as sal_sum from employee. What we are retrieving is, this is an aggregate function, as you all know. We are going to sum salary, if it is null, it's going to use 10,000 as the value, so that the sum of all values in the salary column is added, and the result is displayed as sal_sum. The output is going to be this one, which is the summation of all these salary values. As I already mentioned, null is neither zero nor blank, and that is why if we directly sum this, we might not get the expected result, because this cannot be treated as zero, because null is a special value. But what we have instructed here is, if there is a null value in the salary, replace it with 10,000, so that the summation can be easily performed. Here is a question for you. We know null is neither blank nor zero, but the question is, we want to consider null to be zero. In example two, we considered 10,000 for the replacement of null, in order to do the summation. How to do that? It's simple, just pause this video for a while and think about it. I hope you are done. As I mentioned, it's simple, instead of 10,000, just replace it with zero, so that your summation considers null as a zero. We are done with the is null function. Let's now move on to the next null function, which is coalesce. In English, coalesce means to come together to form one larger group. However, in DBMS, coalesce means it's going to return the first occurred non-null expression. I mean, this function can take multiple expressions, let's say expression one comma, expression two comma, expression three and so on. What this function is going to do is that, it's going to return the first occurred non-null expression among the set of arguments we have passed. Let's see an example, for which I'm going to take the same employee table, and the example number three is select ID comma salary, these are the attributes of the table, ID and salary, and apart from these two columns, I'm going to create one more column in the output, which is coalesce salary, ID. Two parameters I'm going to pass here, and the output of this coalesce salary, ID is stored as a separate column as result, where we are retrieving all these things from employee table, which is this. Now the output is going to be this one. Now, let me explain you how did I get this output. As expected, ID is the first column, salary is the second column, and coalesce function is called, and the result or the output of coalesce function or the return value of this coalesce function is stored as the third column as result. Now, as I mentioned, this coalesce is going to take multiple expressions. Now, in this case, I am using two expressions. Now, this is going to return the first occurred non-null value. In this case, 50,000 comma 101. Let's take this, 50,000 comma 101. Since both are not null values, the first occurred not null value is salary, and that is why it is returning salary as such. Now, let's take the second row, for this row, the input is 40,000 comma 102, which is this. So, the first occurred non-null value is 40,000. Salary is the first attribute. So, it always returns salary as the output, if salary is not null. If salary is null, then the first occurred non-null value will be ID. So, it returns this value as the result, and that is why whenever there is a null value in salary, we are getting ID as the value. Let's assume one more scenario. What if both are null? In that case, it returns null as the output. I mean to say, coalesce is going to return the first occurred non-null value in the set of expressions that we are passing as an argument. If all the arguments are null, then it will return null value. In this case, at least one of the arguments is a non-null value, and that is why we are not getting null value as the output. We are done with the second null function, the coalesce. Now, let's move on to the third null function, which is nullif, for which I'm going to take the same table, employee. Let's see an example now. Example number four, select ID, first name, nullif ID comma new_ID as result from employee. Here, I have done a small modification for understanding nullif. The fourth column, I have made it as a new ID. Let's assume for some reasons, the company has given an ID to each employee, and they have revised the ID as a new ID, and employees are holding some new ID values here. Now, let's see how nullif is going to operate. When we talk about nullif, it's going to compare these two expressions. If both the expressions are the same, I mean, if both the expressions are matching, it returns null. That's what this says. Null if ID is equal to new_ID. It means null if both the expressions are same. So, if this ID and new ID are same, it returns null. Else, what it returns? It returns the first expression, which is ID. So, the output is going to contain three columns, ID, first name, which is ID, first name, and the third one is result. Now, this result is the output of nullif by passing two parameters, ID and new ID. As mentioned, the output of nullif is going to be the first expression if both the expressions are not matching. I mean, they are not equal. If they are equal, then it returns null as the output. Let's see the output now. For the first row, ID 101, and the new ID 201. These two are not matching. I mean, they are different. So, it returns the first value, which is ID, which is 101 in this case. Let's take the second row, which is 102, and here also 102. Since both are matching, it returns null. Similarly, third row, 103, 103. Since both are matching, it returns null. So, the idea behind this is, it compares both the expressions. If both the expressions are the same, it returns null. If they are different, then the value of the first expression is returned as the result. Now, when we talk about this nullif, this nullif can also be used along with select, where, and group by clauses. In case we want the output as 201 here, I mean, we want the output of new ID, not the old ID. What should we do? Just swap the parameters, new ID comma ID. It will meet our requirements. Simply speaking, nullif, meaning null if both the arguments are matching. We are done with the third null function, which is nullif. Let's move on to the last null function, which is ifnull. Remember, the names are different. Nullif and ifnull are different. Now, let's see how ifnull operates. Example, for which I'm taking the employee table, which we use for example one, two, and three, which contains ID, first name, last name, department, and salary. Let's see the example. Example number five, select ID, salary, ifnull salary, 999 as result from employee. Let's see the output. The output is this. Obviously, three columns, ID, salary, and the output of ifnull, which is stored as result. That is why we are getting the third column as result. Now, let's analyze how this ifnull is going to operate. Ifnull is going to replace the null value with a specific value. If there is a null value in salary, it's going to be replaced with 999, say for example, in the salary, for the first row, it is 50,000, so the result is returned as 50,000. For easier navigation, let me refer to the salary column here. However, both are same, because the data for this is actually coming from here, with the modification only in the third column, because this is the output for ifnull. So, 50,000 is not a null value, so it is returned as such. Wherever we have a null value, it is returned as 999, because that is what I have mentioned here. So, ifnull is going to replace the null value with a specific value, whatever we have mentioned in the second argument. If the first argument is null, then it replaces it with this value. If the first argument is not null, then the first argument itself is returned as the return value. I hope all the null functions are clear to you. Before signing out, please take a disclaimer from me. Before implementing all these null functions in the database or before trying all these null functions in the database, I would request you to check the documentation to know whether that null function is supported by that particular software or not. I hope the session is informative, and thank you for watching.

Frequently asked questions

Is NULL the same as zero or a blank string?

No, NULL is a special value indicating missing or unknown data. It is neither zero nor blank.

How can I treat NULL as zero during aggregation?

Use a function like IFNULL(column, 0) or COALESCE(column, 0) inside the aggregate function, such as SUM().

What happens if all arguments in COALESCE are NULL?

If all expressions passed to COALESCE are NULL, the function itself will return NULL.

If NULLIF(A, B) returns NULL, how do I get B instead?

You must swap the parameters: NULLIF(B, A) will return B if A and B are different.