一个选项（非常简单）是交叉连接不同的位置和项，然后将该组合外连接到第二个表。
样本数据：

SQL> with
  2  t1 (h_id) as
  3    (select 1001 from dual union all
  4     select 1002 from dual union all
  5     select 1003 from dual
  6    ),
  7  t2 (h_id, item_id, location, qty) as
  8    (select 1001, 220050, 'S1', 5 from dual union all
  9     select 1002, 220050, 'S2', 1 from dual union all
 10     select 1002, 230050, 'S2', 4 from dual union all
 11     select 1003, 220050, 'S3', 3 from dual union all
 12     select 1003, 230050, 'S3', 2 from dual union all
 13     select 1003, 240050, 'S3', 5 from dual
 14    ),

查询从这里开始：

15  locations as
 16    (select distinct location from t2),
 17  items as
 18    (select distinct item_id from t2)
 19  select i.item_id, l.location, nvl(b.qty, 0) qty
 20  from locations l cross join items i
 21       left join t2 b on b.location = l.location
 22                      and b.item_id = i.item_id
 23  order by i.item_id, l.location;

   ITEM_ID LOCATION          QTY
---------- ---------- ----------
    220050 S1                  5
    220050 S2                  1
    220050 S3                  3
    230050 S1                  0
    230050 S2                  4
    230050 S3                  2
    240050 S1                  0
    240050 S2                  0
    240050 S3                  5

9 rows selected.

SQL>