How to display null records/rows from table

Question

For ex: I have a record with name = "sga_target", and no record exists where name = "_exafusion_enabled". I still want to display a row with "_exafusion_enabled" as name and NULL as value, without using an outer join with any other table.

select name,value from v$spparameter where name 
in('_exafusion_enabled','sga_target');

Desired output :

name                 value
-------------------- -----
sga_target           18G
_exafusion_enabled   null  

(actually name column does not have the value "_exafusion_enabled")

How can we do this?

Answer 1

I'd go about trying to feed the input in using a "pseudo-table" or view .. (as opposed to an IN clause):

here's a very simple example ... if you have more requirements on how to feed the values in, there's lots of examples around the web showing how to build a "table" like w_input, with the data coming in.

  with w_input as (
        select 'sga_target'  name from dual union all
        select '_exafusion_enabled'  from dual
     )
  select wi.name, nvl(sp.value,'null') value
    from w_input  wi
        LEFT OUTER JOIN v$spparameter  sp
           ON sp.name = wi.name
  /


  NAME               VALUE
  ------------------ ------------------------------
  sga_target         1610612736
  _exafusion_enabled null

  2 rows selected.

Answer 2

select name,value 
from v$spparameter 
where name = 'sga_target'
Union all
Select '_exafusion_enabled', Null 
from dual