SQL Interview Question and Answers | Coalesce Function in SQL

Опубликовано: 22 Февраль 2025
на канале: Parag Dhawan

935

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