Return somehting when no record exists, infobright db

Question

I have a query like this and using infobright database --

select field1, field2
from foo
where filed1 in (1,2,3)

I want something to return even if there is no record in table. For example, there is record for filed1 = 2 and filed1 = 2 but nothing exists for filed1 = 3.

How could I make this query so I get something returning from the table for field1 = 3 ? I could use ifnull in case there is a null value in the table for field1 = 3, but what I am trying find if there is absolutely nothing exists.

Answer 1

If field1 is unique you can do this:

SELECT 
  ISNULL(MAX(field1), 'Default F1') as field1, 
  ISNULL(MAX(field2), 'Default F2') as field2
FROM foo
WHERE field1 in (1,2,3)
GROUP by field1

Otherwise you can use UNION like this:

SELECT field1, field2
FROM foo
WHERE filed1 in (1,2,3)
UNION
SELECT 'Default F1' as field1, 'Default F2' as field2
WHERE (SELECT COUNT(*) FROM foo WHERE filed1 in (1,2,3)) = 0

Answer 2

Although this is a short list of ID values you want, you can create a select/union to create the IDs, then left-join to the data table something like

select
      AllIDs.ID,
      foo.field1,
      foo.field2
   from 
      ( select 1 ID union select 2 union select 3 ) as AllIDs
         left join foo
            on AllIDs.ID = foo.field1