Search code examples
sqloracle-databaseouter-join

Oracle add sequential query rows if missing

In Oracle 19c I have a data like:

with t as (
select 1 Cat, 1 id, 11 val from dual
 union all
select 1, 3, 33 from dual
 union all
select 2, 2, 22 from dual
 union all
select 2, 4, 44 from dual)
select * 
  from t

In query result I want to get 4 rows per every cat with ids 1-4 and if there was no such id in that cat a val must be null:

cat id val
1 1 11
1 2
1 3 33
1 4
2 1
2 2 22
2 3
2 4 44
Solution

  • Use a PARTITIONed join with a row-generator:

    SELECT t.cat,
       i.id,
       t.val
FROM   (SELECT LEVEL AS id FROM DUAL CONNECT BY LEVEL <= 4) i
       LEFT OUTER JOIN t
       PARTITION BY (t.cat)
       ON (i.id = t.id)

    Which outputs:

    CAT ID VAL
    1 1 11
    1 2 null
    1 3 33
    1 4 null
    2 1 null
    2 2 22
    2 3 null
    2 4 44

    db<>fiddle here