SQL Interview Question and Answers | Coalesce Function in SQL
The `COALESCE` function in SQL is used to return the first non-null expression among a list of expressions. Its primary purpose is to handle situations where you have multiple expressions or values, and you want to obtain the first non-null value from that list. It's particularly useful in situations where you want to provide a default value when a series of expressions may contain null values.
The basic syntax of the `COALESCE` function is as follows:
```sql
COALESCE(expression1, expression2, expression3, ...)
```
`expression1`, `expression2`, `expression3`, etc.: These are the expressions you want to evaluate in order. The function returns the value of the first expression that is not null. If all expressions are null, the `COALESCE` function returns null.
Here are some common use cases for the `COALESCE` function:
1. **Handling NULL Values**: `COALESCE` is commonly used to replace null values with default values or other non-null values. For example, if you have a column that may contain null values and you want to provide a default value, you can use `COALESCE` to do so.
```sql
SELECT COALESCE(column1, 'Default Value') AS Result
FROM YourTable;
```
2. **Selecting from Multiple Columns**: You can use `COALESCE` to select data from multiple columns in order, returning the first non-null value.
```sql
SELECT COALESCE(column1, column2, column3) AS Result
FROM YourTable;
```
3. **Handling Conditional Expressions**: `COALESCE` can be used in more complex expressions, allowing you to handle conditional logic and return the first non-null result from a set of conditions.
```sql
SELECT COALESCE(
CASE WHEN condition1 THEN value1 END,
CASE WHEN condition2 THEN value2 END,
CASE WHEN condition3 THEN value3 END
) AS Result
FROM YourTable;
```
4. **Aggregating Data**: In some cases, you might want to aggregate data from different columns or sources and return the first non-null value. `COALESCE` can be helpful in such scenarios.
```sql
SELECT COALESCE(SUM(column1), AVG(column2), MAX(column3)) AS Result
FROM YourTable;
```
The `COALESCE` function is database-agnostic and widely supported across various database management systems, including SQL Server, PostgreSQL, MySQL, and Oracle. It provides a convenient way to handle null values and ensure that you obtain a meaningful result even when some data is missing or undefined.
#sql
On this page of the site you can watch the video online SQL Interview Question and Answers | Coalesce Function in SQL with a duration of hours minute second in good quality, which was uploaded by the user Parag Dhawan 26 October 2023, share the link with friends and acquaintances, this video has already been watched 1,044 times on youtube and it was liked by 7 viewers. Enjoy your viewing!
