# Handling Null Values with COALESCE

**URL:** https://meta.discourse.org/t/handling-null-values-with-coalesce/278813
**Category:** Data & reporting
**Tags:** sql-tutorial
**Created:** [September 13, 2023, 1:15am UTC](https://meta.discourse.org/t/handling-null-values-with-coalesce/278813 "2023-09-13T01:15:35Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [September 13, 2023, 1:15am UTC](https://meta.discourse.org/t/handling-null-values-with-coalesce/278813/1 "2023-09-13T01:15:35Z")

</div>

In this tutorial, we will explore the use of the `COALESCE` function in [Data Explorer](https://meta.discourse.org/t/32566?silent=true) SQL queries.

`COALESCE` allows you to handle NULL values in your query results. If you have NULL values in your data, you can use `COALESCE` to provide a default value (like 0) for these NULL values.

`COALESCE` is particularly useful when you might be performing later calculations or data analysis on your query results where NULL values could potentially cause issues or misinterpretations.

# Syntax

The `COALESCE` function takes two or more arguments and returns the first non-NULL value it encounters from left to right in the list. If all arguments are NULL, `COALESCE` returns NULL.

The basic syntax for `COALESCE` is as follows:

```plaintext
COALESCE(value1, value2, ..., valueN)

```

For example, `COALESCE(NULL, 1, 2)` will return `1` because `1` is the first non-NULL argument.

# Example Queries

Let’s examine a few example queries to understand how `COALESCE` is used in [Data Explorer](https://meta.discourse.org/t/32566?silent=true) queries.

## Posts Created, Likes Received, and Bookmarks Received

_Complexity Level: Beginner_

This query will get the total number of posts created, likes received, and bookmarks received on posts for each user on a site. If a user has no posts, likes, or bookmarks received, the `COALESCE` function will return 0 instead of NULL.

```sql
SELECT 
    users.id AS user_id,
    users.username,
    COALESCE(COUNT(posts.id), 0) AS post_count,
    COALESCE(SUM(posts.like_count), 0) AS likes_received,
    COALESCE(SUM(posts.bookmark_count), 0) AS bookmarks_received
FROM 
    users
LEFT JOIN 
    posts ON users.id = posts.user_id
GROUP BY 
    users.id, users.username
ORDER BY 
    post_count DESC, likes_received DESC, bookmarks_received DESC

```

_Example Results:_

| user | username | post\_count | likes\_received | bookmarks\_received |
| --- | --- | --- | --- | --- |
| 1 | alice | 345 | 6 | 9 |
| 2 | bella | 278 | 5 | 6 |
| 3 | charlie | 37 | 3 | 3 |
| 4 | dave | 0 | 0 | 0 |

In this query, we’re joining the `users` table with the `posts` table on the `user_id` field. We’re then using the `COALESCE` function to ensure that if a user has no posts, likes received, or bookmarks received, we return 0 instead of NULL. The results are grouped by user id and username, and ordered by the number of posts, likes, and bookmarks in descending order.

## Topics and Replies per Users

_Complexity Level: Intermediate_

This query gets the count of topics and replies made by each user between two dates. If a user has no topics or replies, `COALESCE` will return 0 instead of NULL.

```sql
-- [params]
-- date :start_date 
-- date :end_date
-- string NULL:username

WITH qtt_topics AS (
    SELECT 
        t.user_id,
        COUNT(*) AS topics
    FROM topics t
    WHERE    
        t.user_id > 0 
        AND t.deleted_at ISNULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY t.user_id
    ),
    
qtt_replies AS (
    SELECT 
        p.user_id,
        COUNT(*) AS replies
    FROM posts p
    WHERE    
        p.user_id > 0 
        AND p.deleted_at ISNULL
        AND p.post_number != 1
        AND p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id
    ),

total AS (
    SELECT
        COALESCE(qr.user_id, qt.user_id) user_id,
        COALESCE(topics,0) qtt_topics,
        COALESCE(replies,0) qtt_replies
    FROM qtt_topics qt
    FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id
    ORDER BY user_id)

SELECT 
    username,
    qtt_topics,
    qtt_replies
FROM total
INNER JOIN users u ON u.id = user_id
WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%'

```

_Example Results:_

| username | qtt\_topics | qtt\_replies |
| --- | --- | --- |
| Alice | 10 | 50 |
| Bella | 15 | 45 |
| Charlie | 12 | 30 |

In this query `COALESCE` is used in the `total` Common Table Expression (CTE). It ensures that if the `user_id` is NULL in either `qtt_topics` or `qtt_replies` , the other value is used. This is important because a FULL JOIN is used to combine `qtt_topics` and `qtt_replies` , and if a user only has topics but no replies (or vice versa), their `user_id` would be NULL in one of the tables. `COALESCE` prevents this from happening.

> **Detailed Explanation with Inline Comments**
>
> ```sql
> -- [params]
> -- date :start_date 
> -- date :end_date
> -- string NULL:username
> 
> -- Define a CTE (Common Table Expression) for counting topics per user
> WITH qtt_topics AS (
> SELECT 
> t.user_id, -- user id
> COUNT(*) AS topics -- count of topics
> FROM topics t -- from topics table
> WHERE    
> t.user_id > 0 -- only consider non-zero user ids
> AND t.deleted_at ISNULL -- only consider topics that are not deleted
> AND t.archetype = 'regular' -- only consider regular topics
> AND t.created_at::date BETWEEN :start_date AND :end_date -- only consider topics created between start_date and end_date
> GROUP BY t.user_id -- group by user id to get the count of topics per user
> ),
>     
> -- Define a CTE for counting replies per user
> qtt_replies AS (
> SELECT 
> p.user_id, -- user id
> COUNT(*) AS replies -- count of replies
> FROM posts p -- from posts table
> WHERE    
> p.user_id > 0 -- only consider non-zero user ids
> AND p.deleted_at ISNULL -- only consider posts that are not deleted
> AND p.post_number != 1 -- only consider posts that are not the first post in a topic (i.e., replies)
> AND p.created_at::date BETWEEN :start_date AND :end_date -- only consider posts created between start_date and end_date
> GROUP BY p.user_id -- group by user id to get the count of replies per user
> ),
> 
> -- Define a CTE to combine the counts of topics and replies per user
> total AS (
> SELECT
> COALESCE(qr.user_id, qt.user_id) user_id, -- user id (from either qtt_replies or qtt_topics)
> COALESCE(topics,0) qtt_topics, -- count of topics (if NULL, return 0)
> COALESCE(replies,0) qtt_replies -- count of replies (if NULL, return 0)
> FROM qtt_topics qt -- from qtt_topics CTE
> FULL JOIN qtt_replies qr ON qt.user_id = qr.user_id -- join with qtt_replies CTE on user id
> ORDER BY user_id -- order by user id
> )
> 
> -- Main query to get the final result set
> SELECT 
> username, -- username
> qtt_topics, -- count of topics
> qtt_replies -- count of replies
> FROM total -- from total CTE
> INNER JOIN users u ON u.id = user_id -- join with users table on user id
> WHERE :username ISNULL OR LOWER(username) LIKE '%'||LOWER(:username)||'%' -- filter by username (if provided)
> 
> ```

## Solved Questions Data

_Complexity Level: Advanced_ / _Requires [Discourse Solved](https://meta.discourse.org/t/discourse-solved/30155)Plugin_

This query is used to get details about topics, including whether they are solved or not, the time taken for the first reply, the time taken for the solution, and other related statistics.

This query assumes all topics on a site can be solved.

```sql
--[params]
-- date :start_date = 2022-01-01
-- date :end_date = 2024-01-01
-- text :tag_name = all

WITH valid_topics AS (
    SELECT 
        t.id,
        t.user_id,
        t.title,
        t.views,
        posts_count-1 AS "posts_count", 
        t.created_at,
        (CURRENT_DATE::date-t.created_at::date) AS "total_days",
        string_agg(tags.name, ', ') AS tag_names
    FROM topics t
    LEFT JOIN topic_tags tt ON tt.topic_id = t.id
    LEFT JOIN tags ON tags.id = tt.tag_id
    LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
    LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
                FROM posts
                WHERE deleted_at ISNULL
                    AND post_type = 1
                    AND post_number > 1
                GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
    WHERE t.deleted_at ISNULL
        AND t.created_at::date BETWEEN :start_date AND :end_date
        AND t.archetype = 'regular'
    GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at
),

solved_topics AS (
    SELECT 
        vt.id,
        tcf.created_at
    FROM topic_custom_fields tcf
    INNER JOIN valid_topics vt ON vt.id = tcf.topic_id
    WHERE tcf.name = 'accepted_answer_post_id'
),

last_reply AS (
    SELECT p.topic_id, p.user_id FROM posts p
    INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                GROUP BY topic_id) x ON x.post = p.id
),

first_reply AS (
    SELECT p.topic_id, p.user_id, p.created_at FROM posts p
    INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p
                WHERE deleted_at ISNULL
                AND post_type = 1
                AND post_number > 1
                GROUP BY topic_id) x ON x.post = p.id
)

SELECT
    CASE 
        WHEN st.id IS NOT NULL THEN 'solved'
        ELSE 'unsolved'
    END AS status,
    vt.tag_names,
    vt.id AS topic_id,
    vt.user_id topic_user_id,
    ue.email,
    vt.title,
    vt.views,
    lr.user_id AS last_reply_user_id,
    ue2.email AS last_reply_user_email,
    vt.created_at::date topic_create,
    COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create,
    COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create,
    COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)",
    COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)",
    COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)",
    COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)",
    posts_count AS number_of_replies,
    total_days AS total_days_without_solution
FROM valid_topics vt
LEFT JOIN last_reply lr ON lr.topic_id = vt.id
LEFT JOIN first_reply fr ON fr.topic_id = vt.id
LEFT JOIN solved_topics st ON st.id = vt.id
INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true
LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true
WHERE (:tag_name = 'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%')
ORDER BY tag_names, total_days DESC

```

_Example Results:_

| status | tag\_names | topic | topic\_user | email | title | views | last\_reply\_user | last\_reply\_user\_email | topic\_create | first\_reply\_create | solution\_create | time\_first\_reply(days) | time\_first\_reply(hours) | time\_solution(days) | time\_solution(hours) | number\_of\_replies | total\_days\_without\_solution |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| solved | a, c, b | A Topic Title (7) | alice | [alice@example.com](mailto:alice@example.com) | A Topic Title | 58 | bella | [bella@example.com](mailto:bella@example.com) | 2023-08-25 | 2023-08-25 | 2023-08-29 | 0 | 1 | 1 | 24 | 9 | 4 |
| unsolved | tag1 | Welcome to the Lounge (3) | system | no\_email | Welcome to the Lounge | 3 | system | no\_email | 2023-05-01 | | | 0 | 0 | 0 | 0 | 2 | 134 |

In this query, `COALESCE` is used in the following lines:

- `COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create`: This line is converting the `created_at` date of the first reply to a string. If the first reply does not exist (i.e., `fr.created_at` is null), it will return an empty string (‘’).
- `COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create`: Similar to the above, this line is converting the `created_at` date of the solution to a string. If the solution does not exist (i.e., `st.created_at` is null), it will return an empty string (‘’).
- `COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)"`: This line is calculating the time difference in days between the creation of the topic and the first reply. If the first reply does not exist (i.e., `fr.created_at` is null), it will return 0.
- `COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)"`: This line is calculating the time difference in hours between the creation of the topic and the first reply. If the first reply does not exist (i.e., `fr.created_at` is null), it will return 0.
- `COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)"`: This line is calculating the time difference in days between the creation of the topic and the solution. If the solution does not exist (i.e., `st.created_at` is null), it will return 0.
- `COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)"`: This line is calculating the time difference in hours between the creation of the topic and the solution. If the solution does not exist (i.e., `st.created_at` is null), it will return 0.

In all these cases, `COALESCE` is used to prevent NULL values from appearing in the final results, which improves the readability of the resulting query and could be helpful for later data processing or analysis.

> **Detailed Explanation with Inline Comments**
>
> ```sql
> --[params]
> -- date :start_date = 2022-01-01
> -- date :end_date = 2024-01-01
> -- text :tag_name = all
> 
> -- Define CTE for valid topics
> WITH valid_topics AS (
> -- Select necessary fields
> SELECT 
> t.id, -- topic id
> t.user_id, -- user id
> t.title, -- topic title
> t.views, -- number of views
> posts_count-1 AS "posts_count", -- number of posts in the topic
> t.created_at, -- creation date of the topic
> (CURRENT_DATE::date-t.created_at::date) AS "total_days", -- total days since the topic was created
> string_agg(tags.name, ', ') AS tag_names -- aggregate all tags associated with the topic
> FROM topics t -- from topics table
> -- Join necessary tables to get tag names
> LEFT JOIN topic_tags tt ON tt.topic_id = t.id
> LEFT JOIN tags ON tags.id = tt.tag_id
> LEFT JOIN tag_group_memberships tgm ON tgm.tag_id = tt.tag_id
> -- Subquery to get the date of the first reply for each topic
> LEFT JOIN (SELECT topic_id, MIN(created_at) AS "created_at"
> FROM posts
> WHERE deleted_at ISNULL
> AND post_type = 1
> AND post_number > 1
> GROUP BY topic_id) date_first_reply ON date_first_reply.topic_id = t.id
> WHERE t.deleted_at ISNULL -- only consider topics that are not deleted
> AND t.created_at::date BETWEEN :start_date AND :end_date -- only consider topics created between start_date and end_date
> AND t.archetype = 'regular' -- only consider regular topics
> GROUP BY t.id, t.user_id, t.title, t.views, posts_count, t.created_at -- group by necessary fields to get the correct count
> ),
> 
> -- Define CTE for solved topics
> solved_topics AS (
> -- Select topic id and creation date of the solution
> SELECT 
> vt.id,
> tcf.created_at
> FROM topic_custom_fields tcf -- from topic_custom_fields table
> INNER JOIN valid_topics vt ON vt.id = tcf.topic_id -- join with valid_topics CTE
> WHERE tcf.name = 'accepted_answer_post_id' -- only consider topics with an accepted answer
> ),
> 
> -- Define CTE for the last reply of each topic
> last_reply AS (
> -- Select topic id and user id of the last reply
> SELECT p.topic_id, p.user_id FROM posts p
> INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p -- subquery to get the id of the last post for each topic
> WHERE deleted_at ISNULL
> AND post_type = 1
> GROUP BY topic_id) x ON x.post = p.id -- join with posts table to get the user id of the last reply
> ),
> 
> -- Define CTE for the first reply of each topic
> first_reply AS (
> -- Select topic id, user id and creation date of the first reply
> SELECT p.topic_id, p.user_id, p.created_at FROM posts p
> INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p -- subquery to get the id of the first reply for each topic
> WHERE deleted_at ISNULL
> AND post_type = 1
> AND post_number > 1
> GROUP BY topic_id) x ON x.post = p.id -- join with posts table to get the user id and creation date of the first reply
> )
> 
> -- Main query to get the final result set
> SELECT
> CASE 
> WHEN st.id IS NOT NULL THEN 'solved' -- if the topic id is in solved_topics CTE, then the status is 'solved'
> ELSE 'unsolved' -- otherwise, the status is 'unsolved'
> END AS status,
> vt.tag_names, -- tag names
> vt.id AS topic_id, -- topic id
> vt.user_id topic_user_id, -- user id
> ue.email, -- user email
> vt.title, -- topic title
> vt.views, -- number of views
> lr.user_id AS last_reply_user_id, -- user id of the last reply
> ue2.email AS last_reply_user_email, -- email of the user who made the last reply
> vt.created_at::date topic_create, -- creation date of the topic
> COALESCE(TO_CHAR(fr.created_at,'YYYY-MM-DD'),'') AS first_reply_create, -- creation date of the first reply, if exists, otherwise empty string
> COALESCE(TO_CHAR(st.created_at,'YYYY-MM-DD'),'') AS solution_create, -- creation date of the solution, if exists, otherwise empty string
> COALESCE(fr.created_at::date - vt.created_at::date,0) AS "time_first_reply(days)", -- time taken for the first reply in days
> COALESCE(CEIL(extract(epoch from (fr.created_at - vt.created_at))/3600.00),0) as "time_first_reply(hours)", -- time taken for the first reply in hours
> COALESCE(st.created_at::date - vt.created_at::date,0) AS "time_solution(days)", -- time taken for the solution in days
> COALESCE(CEIL(extract(epoch from (st.created_at - vt.created_at))/3600.00),0) as "time_solution(hours)", -- time taken for the solution in hours
> posts_count AS number_of_replies, -- number of replies
> total_days AS total_days_without_solution -- total days without solution
> FROM valid_topics vt -- from valid_topics CTE
> LEFT JOIN last_reply lr ON lr.topic_id = vt.id -- join with last_reply CTE
> LEFT JOIN first_reply fr ON fr.topic_id = vt.id -- join with first_reply CTE
> LEFT JOIN solved_topics st ON st.id = vt.id -- join with solved_topics CTE
> INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true -- join with user_emails table to get the email of the user
> LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true -- join with user_emails table to get the email of the user who made the last reply
> WHERE (:tag_name = 'all' OR LOWER(vt.tag_names) LIKE '%' || LOWER(:tag_name) || '%') -- filter by tag name
> ORDER BY tag_names, total_days DESC -- order by tag names and total days in descending order
> 
> ```

* * *

If you have any questions or examples of how you’ve used `COALESCE` in your [Data Explorer](https://meta.discourse.org/t/32566?silent=true) queries, please feel free to share them below. :slightly_smiling_face: