1

What would be the most practical way to write a query, that returns single row in case of empty result set.

Something like (pseudosql)

(SELECT 1 AS c1, 2 AS c2 FROM ... WHERE ...) OR_ELSE(SELECT 0 AS c1, 0 AS c2)

4 Answers 4

1

You can use either generate_series function or VALUES Lists with a left join. The sample queries are below;

    CREATE TABLE my_table (a INT, b INT);
    INSERT INTO my_table VALUES (1,2), (2,2), (3,4);

    SELECT a, b FROM generate_series(1,1)
    LEFT JOIN my_table ON b = 5;

    |      a |      b |
    |--------|--------|
    | (null) | (null) |


    SELECT a, b FROM (values (1)) as one
    LEFT JOIN my_table ON b = 5;

    |      a |      b |
    |--------|--------|
    | (null) | (null) |


    SELECT a, b FROM generate_series(1,1)
    LEFT JOIN my_table ON b = 2;

    | a | b |
    |---|---|
    | 1 | 2 |
    | 2 | 2 |
2
  • 1
    You could also use (VALUES (1)) AS one instead of generate_series(1,1). But why the LIMIT 1? I don't think we need it. May 6, 2018 at 18:43
  • @ypercubeᵀᴹ You are right, I am improving the answer
    – Sahap Asci
    May 6, 2018 at 21:22
0

If some column is null you could use COALESCE():

SELECT COALESCE(C1, 0) as C1,
       COALESCE(C2, 0) AS C2,
       ...
FROM   YOUR_TABLE
WHERE  (Conditions);

If there isn't any row that match the conditions you could use a UNION plus a LIMIT. But you should add new column just to ensure rows are returned using the desired order.

SELECT C1, C2, ... 
FROM (
       SELECT COALESCE(C1, 1) as C1,
              COALESCE(C2, 2) AS C2,
              ...
       FROM   YOUR_TABLE
       WHERE  (Conditions)
       UNION ALL
       SELECT 0 AS C1,
              0 AS C2,
              ...
      ) T
LIMIT 1;

db<>fiddle here

6
  • UNION + LIMIT + ORDER is what I had in mind, but I wonder if there's a better way... would it be possible to generalize it to plsql func?
    – KadekM
    May 4, 2018 at 11:09
  • Also the LIMIT 1 wouldn't be correct, as first query may need to return multiple rows.
    – KadekM
    May 4, 2018 at 12:22
  • But, if your query doesn't return any row that match the criteria, why do you need a row?
    – McNets
    May 4, 2018 at 13:18
  • Default values - as it's part of much bigger query.
    – KadekM
    May 4, 2018 at 13:31
  • Could you add a mcve?
    – McNets
    May 4, 2018 at 13:34
0

Had to work around Excel's stupid bug where if a table is empty then you cannot do sums of columns because the table contains null and it thinks it is text...

In my case I had one column (widget_id in my example) that couldn't be null and I used that to distinguish the dummy row. But if you can't have a special value then you'll have to add a column.

with widgets_fixed as (
    select
        widget_id,
        widget_name,
        widget_price
    from widgets
union all
    select
        null as widget_id,
        null as widget_name,
        0 as widget_price
)
select * from widgets_fixed
where case 
    when (
        select count(*) 
        from widget_fixed 
        where widget_id is not null
    ) > 0 
        then (widget_id is not null) 
    else true
end
-1

This will return one row of nulls if mytable is empty and a normal table if it has rows:

SELECT mytable.* FROM (SELECT 1 AS one) AS dummy
LEFT JOIN (SELECT * FROM myschema.mytable /*WHERE FALSE*/) AS mytable ON (true)
2
  • That’s the same as McNet’s answer, from 4 years ago.
    – dwhitemv
    Dec 1, 2022 at 15:02
  • It is different, you don't have to list columns you can use select * . I use it in c# project to get the list of query's columns.
    – plavozont
    Feb 17, 2023 at 3:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.