20

Consider this select statement:

SELECT *, 
       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.

5 Answers 5

27
SELECT col1, 
       col2, 
       col3, 
       1 AS query_id 
FROM players 
WHERE username='foobar'
union all 
select null,
       null,
       null,
       1
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.*, 
       qid.query_id
from qid 
  left join players as p on (p.useranme = 'foobar');

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

select p.*, 
       qid.query_id
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).

12
  • 1
    In trying out the first example, it seems the ALL keyword is not needed? Commented Apr 27, 2013 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.
    – user1822
    Commented Apr 27, 2013 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?
    – user1822
    Commented Apr 28, 2013 at 5:41
  • 3
    @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
    – user1822
    Commented Apr 28, 2013 at 7:59
  • 5
    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. Commented Jul 16, 2013 at 15:52
8

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

SELECT
  max(col1) col1,
  max(col2) col2, 
  1 AS query_id 
FROM
  players 
WHERE
  username='foobar';

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

4
  • 3
    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'
    – user1822
    Commented Apr 27, 2013 at 20:56
  • 1
    Could coalesce() also be used in this fashion? Commented Apr 27, 2013 at 23:09
  • 1
    Coalesce would not generate a row where none is projected from the table. Commented Apr 28, 2013 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. Commented Apr 28, 2013 at 17:30
4

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

-1

i needed to do something similar and my brain was not giving, so i searched and found this question ... but then thought of this way also, if username is unique in players ... which in my use fits better (but surely it can be simplified more ?! :)

     WITH _ AS ( SELECT * FROM players
                         WHERE username = 'foobar' )
       , __ AS ( SELECT CASE WHEN EXISTS ( TABLE _ )
                             THEN ( SELECT    _::players FROM _ )
                             ELSE ( SELECT NULL::players )
                              END _ )
   SELECT (__._).*
                , 1 AS query_id
     FROM __
     ;
4
  • Looks inefficient, you're querying the table twice Commented Apr 2, 2021 at 2:02
  • do you mean querying the CTE from the WITH clause twice? i was thinking that may get optimised out ... one of the suggestions in the top answer seems to query the actual table twice ... i am not sure about that ... maybe will test and check the plan. Anyeway, this way provides my purpose with more flexibility in the more complex version i need to use ...
    – sol
    Commented Apr 2, 2021 at 8:40
  • The third version of the accepted answer is what I use, it's very compact and I find it logical: just use a values as the driving side of a left join, and no extra table scan Commented Apr 2, 2021 at 10:09
  • ... ok cool going to check it, thanks ... i think i was also a little obsessed with using 'query_id' only once as an identifier !! ... if i get any time i may start to look at query plans ... my usage is for quite small configuration tables and where an inheriting table has an extra column, i am avoiding synamic SQL as much as possible also ... so really my need is to get a row whatever and maybe update and move it to be part of an inheriting table ... lets see ...
    – sol
    Commented Apr 2, 2021 at 13:29
-2
select isnull(column,1) from table
1
  • 2
    This will not produce an extra row when the query returns 0 rows. Commented Feb 10, 2016 at 10:20

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.