Menu
If I have a simple query
SELECT row FROM table WHERE id=my_id
and I want a row with a NULL instead of an empty row if my_id isn't found, I can use coalesce
COALESCE( (SELECT row FROM table WHERE id=my_id), NULL)
What do I do if my query returns multiple columns and I want a row of NULLs?
SELECT row1,row2,row3 FROM table WHERE id=my_id
343
Concatenate Rows Using COALESCE All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable.
Because the COALESCE is an expression, you can use it in any clause that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING .
The COALESCE scalar function takes two or more arguments and returns the first nonnull argument, starting from the left in the expression list. The COALESCE function returns the value of one of the expressions in the list.
COALESCE promotes its argument to the higher data type. In the first SQL query, we have used ISNULL and the first data type is INT but because of its NULL, it also converts 3.00 to INT and performed integer arithmetic, but COALESCE correctly promotes the 19 to FLOAT and performed floating-point arithmetic.
You can create a pseudo table with UNNEST for your parameter and do a left outer join:
SELECT t.row1, t.row2, t.row3
FROM UNNEST(ARRAY[my_id]) i LEFT OUTER JOIN table t ON t.id = i;
Another solution would be to simply search the table three times
SELECT
COALESCE( (SELECT row1 FROM table WHERE id=my_id), NULL),
COALESCE( (SELECT row2 FROM table WHERE id=my_id), NULL),
COALESCE( (SELECT row3 FROM table WHERE id=my_id), NULL)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With