How to return a value with COALESCE when a SELECT statement returns nothing?

user_15612172 0
Viewed 158

Hello Stackoverflow community !

I got this code :

(SELECT COALESCE(id, 0) FROM members WHERE user_id = 1225282512438558720)
UNION ALL
(SELECT COALESCE(id, 0) FROM channels WHERE channel_id = 720694686028791971)
UNION ALL
(SELECT COALESCE(id, 0) FROM guilds WHERE guild_id = 831900150115991605);

Each statement could or not could return a value (because nothing corresponds to the WHERE Clause)

My problem is that if for example the first statement returns nothing then Postgres is going to return me this

 coalesce
----------
        6
        1

but i want that Postgres returns me this :

 coalesce
----------
     NULL
        6
        1

How can i do that ?

postgresql sql
2 Answers

This query returns nothing because no rows satisfy filter criteria. To return rows from the empty result set you need to do aggregation. So you need:

(SELECT max(id) FROM members WHERE user_id = 1225282512438558720)
UNION ALL
(SELECT max(id) FROM channels WHERE channel_id = 720694686028791971)
UNION ALL
(SELECT max(id) FROM guilds WHERE guild_id = 831900150115991605);
user_27787100

For multiple columns or results, you can left-join an unary row table to each of your inputs

WITH dual AS (select 1)
(SELECT COALESCE(id, 0) FROM dual LEFT JOIN members ON user_id = 1225282512438558720)
UNION ALL
(SELECT COALESCE(id, 0) FROM dual LEFT JOIN channels ON channel_id = 720694686028791971)
UNION ALL
(SELECT COALESCE(id, 0) FROM dual LEFT JOIN guilds ON guild_id = 831900150115991605);
user_76355690
Related