SQL> select sum(nvl(sal,0)), deptno from emp where deptno = 0 group by deptno;

no rows selected

SQL> select sum(nvl(sal,0)) from emp where deptno = 0;
SUM(NVL(SAL,0))
---------------


1 row selected.

SQL> select * from t_emp order by region;

    REGION        SAL
---------- ----------
        10     2572.5
        10       5250
        10       1365
        20    3198.13
        20       3225
        20     1182.5
        20        860
        20       3225
        30       1375
        30       1650
        30       1760
        30       1045
        30       3135
        30       1375

14 rows selected.

SQL> select region, sum(sal) from t_emp group by region;

    REGION   SUM(SAL)
---------- ----------
        20   11690.63
        30      10340
        10     9187.5         --> remember this value

SQL>

SQL> with temp (region) as
  2    (select 1000 deptno from dual)
  3  select t.region,
  4         nvl(sum(e.sal), 0) c_sum
  5  from temp t left join t_emp e on t.region = e.region
  6  group by t.region;

    REGION      C_SUM
---------- ----------
      1000          0             --> there are no rows for region 1000 in T_EMP, so
                                      query returned a row whose sum is 0

SQL> with temp (region) as
  2    (select 10 deptno from dual)
  3  select t.region,
  4         nvl(sum(e.sal), 0) c_sum
  5  from temp t left join t_emp e on t.region = e.region
  6  group by t.region;

    REGION      C_SUM
---------- ----------
        10     9187.5

SQL>