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


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 |
  • 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. Commented May 6, 2018 at 18:43
  • @ypercubeᵀᴹ You are right, I am improving the answer
    – Sahap Asci
    Commented May 6, 2018 at 21:22

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

       COALESCE(C2, 0) AS C2,
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, ... 
       SELECT COALESCE(C1, 1) as C1,
              COALESCE(C2, 2) AS C2,
       WHERE  (Conditions)
       UNION ALL
       SELECT 0 AS C1,
              0 AS C2,
      ) T

db<>fiddle here

  • 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
    Commented May 4, 2018 at 11:09
  • Also the LIMIT 1 wouldn't be correct, as first query may need to return multiple rows.
    – KadekM
    Commented 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
    Commented May 4, 2018 at 13:18
  • Default values - as it's part of much bigger query.
    – KadekM
    Commented May 4, 2018 at 13:31
  • Could you add a mcve?
    – McNets
    Commented May 4, 2018 at 13:34

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 (
    from widgets
union all
        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

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)
  • That’s the same as McNet’s answer, from 4 years ago.
    – dwhitemv
    Commented 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
    Commented 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.