Including not found criteria in the result

Question

I have this query

select  BUnit, value from myTable where BUnit in (555,556,557,558)

and this values for Bunit are constructed dynamicaly, ie. a program builds queries on demand, and not always this will be the selected numbers.

A possible query result would be

Bunit            value
556              10.2
558              5.18

However, I would to construct a select statement to return

Bunit            value
556              10.2
558              5.18  
555              NULL
557              NULL

I am not allowed to create tables (temporary or not) and to create procedures. How can I do it?

Answer 1

Without a temporary table, you can't since you must find a way to put all the values of BUnit into a table of some kind and then join myTable against that table to get the NULL values.

You could generate the SQL dynamically in your app and send down something like this:

select 555, (select value from myTable where BUnit = 555)
from dual
union
select 556, (select value from myTable where BUnit = 556)
from dual
...

But before you attempt this, I suggest that you create the desired result structure in your app and then fill in the values you get from the database.

Answer 2

You can build a table with the criteria, and join on that:

select criteria.BUnit, t.value 
from (
    select 555 as BUnit from dual
    union select 556 from dual
    union select 557 from dual
    union select 558 from dual
) criteria
left join myTable t 
    on t.BUnit = criteria.BUnit