Consider this select statement:

       1 AS query_id 
FROM players 
WHERE username='foobar';

It returns the column query_id with value 1 along with a player's other columns.

How would one make the above SQL return at least the query_id of 1 even if the select finds no rows that match?

BTW, it's PostgreSQL 8.4.

SELECT col1, 
       1 AS query_id 
FROM players 
WHERE username='foobar'
union all 
select null,
where not exists (select 1 from players where username = 'foobar');

Or as an alternative (might be faster as no second subselect is required):

with qid (query_id) as (
   values (1)
select p.*, 
from qid 
  left join players as p on (p.useranme = 'foobar');

You can re-write the above to a more "compact" representation:

select p.*, 
from (values (1)) as qid (query_id)
  left join players as p on (p.useranme = 'foobar');

But I think the explicit CTE (with...) is more readable (although that is always in the eyes of the beholder).

  • 1
    In trying out the first example, it seems the ALL keyword is not needed? – Nathanael Weiss Apr 27 '13 at 19:15
  • 2
    @NatWeiss: if you need a specific order, you have to supply an order by. The second one "creates" a virtual table with exactly one row and one column and does an outer join it (without any "real" join condition), thus you always get back at least that one row. Using select * in production code is bad style. Don't do it. Always list the columns you need. select * should only be used in ad-hoc queries. – a_horse_with_no_name Apr 27 '13 at 20:48
  • 2
    @NatWeiss: what "alternate syntax" for "other joins" are you referring to. And why do you think left join is not readable? – a_horse_with_no_name Apr 28 '13 at 5:41
  • 2
    @NatWeiss: the implicit join in the where clause is bad coding style and should be avoided. It can lead to unwanted cartesians joins without giving you an error. And it clearly separates the two (relational) concepts of joining and filtering – a_horse_with_no_name Apr 28 '13 at 7:59
  • 4
    re: the "all" modifier of the "union" clause not being needed: UNION ALL can sometimes be more efficient than UNION, as you are explicitly telling the query planner that either you expect there to be no duplicate rows coming out of the UNIONed queries or if there are you want them to be output. Without the ALL modifier it assumes you want duplicate rows removing (only one of each returned) much like with the DISTINCT keyword, and to guarantee that it may need to resort+rescan the results an extra time. So use ALL with UNION unless you specifically need output row de-duplication. – David Spillett Jul 16 '13 at 15:52

If you are only expecting one or zero rows back, then this would also work:

  max(col1) col1,
  max(col2) col2, 
  1 AS query_id 

This will return one row with all values having null except query_id if no row is found.

  • 2
    Nice trick. The only drawback is that the values for col1 and col2 might not belong to the same row, if there is more than one matching the condition username = 'foobar' – a_horse_with_no_name Apr 27 '13 at 20:56
  • 1
    Could coalesce() also be used in this fashion? – Nathanael Weiss Apr 27 '13 at 23:09
  • 1
    Coalesce would not generate a row where none is projected from the table. – David Aldridge Apr 28 '13 at 17:28
  • 1
    @a_horse_with_no_name yes, although the table and column names suggest that the predicate is on a candidate key for the table, so zero or one row would be projected. – David Aldridge Apr 28 '13 at 17:30

Chiming in way late here, but here's a syntax that works (at least in 9.2, haven't tried earlier versions).

SELECT (COALESCE(a.*,b.*::players)).*
FROM ( SELECT col1,  col2,  col3, 1 AS query_id 
       FROM players WHERE username='foobar' ) a
RIGHT JOIN (select null col1, null col2, null col3, 1 col4) b
ON a.query_id = b.col4;

Will only return the "blank" row if the entire contents of "a" is null.

Enjoy. /bithead

select isnull(column,1) from table
  • 2
    This will not produce an extra row when the query returns 0 rows. – ypercubeᵀᴹ Feb 10 '16 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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