Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

3 Answers 3

up vote 6 down vote accepted
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).

share|improve this answer
    
In trying out the first example, it seems the ALL keyword is not needed? –  Nat 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? –  Nat 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? –  Nat Weiss Apr 27 '13 at 19:18
    
@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
1  
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:

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.

share|improve this answer
    
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
    
Could coalesce() also be used in this fashion? –  Nat Weiss Apr 27 '13 at 23:09
    
Coalesce would not generate a row where none is projected from the table. –  David Aldridge Apr 28 '13 at 17:28
    
@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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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