Return full list, even when not found

[TyzA]

Hi,

Is the following possible:

select *
from table_x
where field_x in (value_x, value_y, value_z)

value_y doesn't exist in the table thus logically speaking the query would only return the results for value_x and value_z.

Now my questions is, can I also retrieve a blank row for value_y via a select statement?
The result would be like:
row value_x
row value_y (all null values)
row value_z

With stored procedures/functions this is easy to but unfortunately I can't make use of these.

Any idea's.

Tx,
Tijs

 

[hoinz]

Something like that usually is done by means of an outer join;
the following may look a bit ugly, but it should work:

select d.field_x, t.other_field from table_x t
right outer join
(
select 'value_x' as field_x from dual
UNION
select 'value_y' as field_x from dual
UNION
select 'value_z' as field_x from dual
) d
on t.field_x = d.field_x