EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 COALESCE

DB2 COALESCE

Updated March 6, 2023

DB2 COALESCE

Introduction to DB2 COALESCE

DB2 COALESCE is used to handle the NULL values that might be present in the data or list of values that are specified. When the data is stored in DB2 RDBMS, we use the tables and store the data in rows and columns format. If the NOT NULL constraint on the column is not applied, then the default value that gets inserted in those columns when not specified is the NULL value. While retrieving and displaying the data to the user, it is not good to display certain columns’ NULL valueshat case, we can use the COALESCE function in DB2, which will help us get the first non NULL value from the list of the parameters specified while using it.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax:

The syntax of the COALESCE function in DB2 is as shown below:

COALESCE(expr1,expr2,expr3,…,exprn)

In the above syntax, the specification of atleast a single value is compulsory. However, we can specify the other values optionally. The expr are the expressions that can be any constant value of even the column’s name whose value you wish to retrieve. The coalesce function searches for the non-null value in all the specified parameters expressions. Whichever expression evaluates to non-null value first, that value is considered the resultant and returned as an output. However, note that if all the coalesce function’s expressions evaluate to null, it returns the NULL value.

Examples of DB2 COALESCE

Given below are the example of DB2 COALESCE:

Use of COALESCE function to retrieve the first non-NULL value from the list of the values that are specified as the fixed values.

Let us first consider the use of COALESCE function with the list of values (NULL, NULL, NULL, ‘COALESCE function’, NULL, ‘EDUCBA’)

Code:

SELECT COALESCE (NULL, NULL, NULL, 'COALESCE function', NULL, 'EDUCBA');

The execution of the above query statement gives the following output as the resultant and gives out the first non-null value from the list of specified values in the coalesce parameters.

Output:

DB2 COALESCE OP1

Now, consider that we remove the COALESCE function string from the list and place the NULL value in its place. So, the first non-null value from the list will be EDUCBA. Hence, the execution of the following query statement gives out EDUCBA as the output.

Code:

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, 'EDUCBA');

The output of the execution of the above query statement is as shown in the below image.

Output:

DB2 COALESCE OP2

Passing NULL values in the list.

If we supply all the parameters as NULL, then it gives out the error. The COALESCE function goes for searching the first non- NULL value and fails in finding the same. Hence it throws an error saying that it should contain atleast one argument, which is having the non-NULL value.

Code:

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL);

Execution of the above statement gives the following error shown in the image.

Output:

Passing NULL values in the list

Use of COALESCE function for substituting the NULL values stored in columns of the table.

The main purpose of the COALESCE function is to display any non- NULL value in place of NULL values when a particular column of the table is given the NULL constraint or not provided the NOT NULL constraint. In case if default value is not specified for that column or the column is kept to AUTOINCREMENT value, then by default, the value that gets inserted for that column is the NULL value. Suppose that we have one table named Sales_Customers.

If we retrieve the data of the table sales_customers from the database right now by using the following query statement.

Code:

SELECT * FROM [Sales_Customers];

Execution of the above statement gives the following result with all the values of the column purchase_date having the value NULL for store_id column having the value of FRUITS in it as shown below.

Output:

for substituting the NULL values

If we have to replace the value of NULL in the purchase_date column, then we can do that by using the COALESCE function by giving the first argument as the name of the column of purchase_date, and then the second argument can be any non-NULL value with which we want to replace the NULL value with. The COALESCE function will search the first non-NULL value; if the column has any date stored in it, it displays that date; else, it goes further to replace that null value stored in the column with the value specified in the second parameter. Suppose that we have to show the default date as “21-03-2021”. Then we can use the COALESCE function while retrieving the table’s values by using the following SELECT query statement.

Code:

SELECT customer_id, f_name , email_id , mobile_number , purchase_date , store_id , bill_amount , COALESCE(purchase_date,"21-03-2021") FROM [Sales_Customers];

The above query statement’s outputhown below with replacing the values of the purchase date column with null values with the date that we have specified in the last value retrieved in the select query.

Output:

DB2 COALESCE 5

We can also make the use of the COALESCE function to replace the column value having NULL in it with some other column value by specifying the column containing NULL values as the first argument and the column with which the value needs to be replaced in the second argument.

Consider an example where we have the table named workers created by using the following query statement.

Code:

CREATE TABLE workers(
employee_id INT NOT NULL ,
f_name VARCHAR(50) NULL,
l_name VARCHAR(50) NULL,
email_id VARCHAR(50) NULL,
mobile_no INT NULL,
joining_date VARCHAR(50) NULL,
food_intake BOOLEAN NULL,
salary FLOAT(10, 2) NULL,
address1 VARCHAR(50) NULL,
address2 VARCHAR(50) NULL,
PRIMARY KEY (employee_id )
)

The data of the table is as shown below:

Code:

SELECT * FROM [workers]

Output:

data of the table

Using the following query statement, we can display the column address2 value in address1 place if it is NULL, as shown below.

Code:

SELECT employee_id,f_name, email_id, salary, COALESCE(address1,address2) FROM [workers]

Output:

DB2 COALESCE 7

Conclusion

We can use COALESCE function to handle the NULL values present in the table columns or present in the list for replacement or substitution with some other value.

Recommended Articles

This is a guide to DB2 COALESCE. Here we discuss the introduction and examples of DB2 COALESCE for a better understanding. You may also have a look at the following articles to learn more –

  1. What is DB2?
  2. Database Management Software
  3. Data Analysis Tools
  4. Data Modelling Tools
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW