Getting SELECT to return a constant value even if zero rows match

Question

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.

score 26 · Accepted Answer · 2013-04-28T05:46:10.927

26

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).

edited Apr 28 '13 at 05:46

answered Apr 27 '13 at 08:24

1

In trying out the first example, it seems the ALL keyword is not needed? – Nathanael Weiss Apr 27 '13 at 19:15
I also learned that if the "WHERE NOT EXISTS..." phrase is removed, the select returns two rows on a successful match. In reading the manual, it seems that these two rows could be in any order? – Nathanael Weiss Apr 27 '13 at 19:16
I like the second sql statement better because it's shorter, more elegant, doesn't require the spelling out of every column, and doesn't require a bunch of nulls. Can you provide any commentary on how the statement is working? – Nathanael Weiss Apr 27 '13 at 19:18
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. – Apr 27 '13 at 20:48
Okay. I noticed with other types of joins that there is an alternate syntax. Is there any type of alternate syntax for the above left join that makes the sql more readable? – Nathanael Weiss Apr 27 '13 at 23:08
@NatWeiss What constitutes "more readable"? – Mark Storey-Smith Apr 28 '13 at 02:12
You know, more simple, natural, easier to understand. – Nathanael Weiss Apr 28 '13 at 03:55
2

@NatWeiss: what "alternate syntax" for "other joins" are you referring to. And why do you think left join is not readable? – Apr 28 '13 at 05:41
For example, an inner join can be written as SELECT * FROM weather INNER JOIN cities ON (weather.city = cities.name); or SELECT * FROM weather, cities WHERE city = name;. Some people might find one or the other version more readable or easier to understand. Thanks for updating your answer with the alternate syntax and thanks for the great answer itself. – Nathanael Weiss Apr 28 '13 at 07: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 – Apr 28 '13 at 07: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. – David Spillett Jul 16 '13 at 15:52
Consider that, for a string type constant value not forget to put single quotations. – ConductedClever Oct 09 '19 at 09:56

score 8 · Answer 2 · answered Apr 27 '13 at 19:05

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.

answered Apr 27 '13 at 19:05

David Aldridge

2,178
11
14

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' – 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

Kirk Roybal · Answer 3 · 2014-12-02T22:09:22.360

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

score -1 · Answer 4 · answered Apr 01 '21 at 22:14

-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 __
     ;

answered Apr 01 '21 at 22:14

sol

109
2

Looks inefficient, you're querying the table twice – Charlieface Apr 02 '21 at 02: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 Apr 02 '21 at 08: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 – Charlieface Apr 02 '21 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 Apr 02 '21 at 13:29

score -2 · Answer 5 · edited Feb 10 '16 at 16:15

-2

select isnull(column,1) from table

edited Feb 10 '16 at 16:15

Andriy M

22,983
6
59
103

answered Feb 10 '16 at 10:14

user86795

9

2

This will not produce an extra row when the query returns 0 rows. – ypercubeᵀᴹ Feb 10 '16 at 10:20

Getting SELECT to return a constant value even if zero rows match

5 Answers5