PostgreSQL

Chapter 7 - DQL (Data Query Language)

COALESCE

The COALESCE function in PostgreSQL is used to handle null values in queries. It returns the first non-null expression among its arguments. This function is particularly useful when dealing with optional data or when you want to provide a default value in case of null entries.

Here’s a brief overview of how COALESCE works:

Basic Syntax:

COALESCE(expression1, expression2, ..., expressionN)

How It Works:
- COALESCE evaluates the expressions from left to right.
- It returns the first expression that is not null.
- If all expressions are null, it returns null.
Example Usage: Suppose you have a customers table and you want to select customer names and a fallback value if the name is not available:
```
SELECT
  COALESCE(name, 'Unknown') AS customer_name
FROM
  customers;
```

In this query:

If name is null, the result will be 'Unknown'.
If name is not null, it will display the actual name.

Example with Multiple Expressions: You can use COALESCE with multiple expressions to provide a chain of fallback values. For example, if you have a table of accounts where some fields might be null, you could use:
```
SELECT
  COALESCE(account_number, 'No Account Number', 'N/A') AS
  account_info
FROM
  accounts;
```

In this query:

If account_number is null, it will check for the next fallback value, which is 'No Account Number'.
If that is also null, it will finally fallback to 'N/A'.

Handling Nulls in Joins: When joining tables, you might encounter null values in certain columns. Using COALESCE can help provide default values in such cases:

SELECT
  COALESCE(c.name, 'Anonymous') AS customer_name,
  COALESCE(a.balance, 0) AS account_balance
FROM
  customers c
LEFT JOIN
  accounts a ON c.customer_id = a.customer_id;

Here:

COALESCE(c.name, 'Anonymous') ensures that if a customer’s name is null, it will display 'Anonymous'.
COALESCE(a.balance, 0) ensures that if the account balance is null, it will display 0.

Using COALESCE effectively helps in making your queries more robust and user-friendly by handling null values gracefully.

Tansy SQL Course - COALESCE - Video Thumbnail

TEST CODE

To replace descriptions with the string 'Not Provided' wherever the description is null, use the following query:

SELECT product_id, product_name, COALESCE(description, 'Not Provided') AS description
FROM prd_product
ORDER BY product_id;

The COALESCE function in SQL returns the first non-null expression among its arguments. This function is useful for handling cases where data may be missing and a default value is needed.

Syntax of COALESCE

COALESCE(expression1, expression2, ..., expressionN)

The function evaluates the expressions in order.
It returns the first non-null expression.
If all expressions are null, COALESCE returns null.

Example Usage of COALESCE

Here's an SQL query example using COALESCE:

SELECT COALESCE(address, phone, email, 'Unknown') AS contact_info FROM customers;

This example attempts to find the first non-null contact information for each customer and defaults to 'Unknown' if all are null.

COALESCE with LEFT JOIN

COALESCE can be used with LEFT JOIN to provide a default value when the joined table has no match:

    SELECT customers.name, COALESCE(orders.order_number, 'No Orders') AS order_status
    FROM customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id;

In this scenario, if a customer has no orders, 'No Orders' is returned instead of null.

COALESCE EXAMPLE

Here, we are attempting to substitute descriptions with the string 'Not provided' in cases where the description is null.

Please note that the query has successfully replaced null descriptions for product IDs 3 and 6 with the desired string 'Not Provided'. However, descriptions for product IDs 7 and 9 remain as empty strings. It's important to differentiate between an empty string and NULL. Our query was designed to replace NULL values, not empty strings.

Previous Next

Comments(0 comments)

Comments Not Found