Oracle 12C SQL - Populate missing results with Joins

Question

I have the following table of data (simplified for example)

TEAM    WORKS   TT
TEAM_A  JOB_1   10
TEAM_A  JOB_3   20
TEAM_B  JOB_2   30

And i wish to get the following results

TEAM    WORKS   TT
TEAM_A  JOB_1   10
TEAM_A  JOB_2   NULL
TEAM_A  JOB_3   20
TEAM_B  JOB_1   NULL
TEAM_B  JOB_2   30
TEAM_B  JOB_3   NULL

This results table needs to add in, for each team, the type of work that does not appear in the original data set, and give the time value NULL

I have got the following SQL as an attempt and have tried many join types, but can never get my desired results.

WITH BASE AS (
            SELECT 'TEAM_A' AS TEAM, 'JOB_1' AS WORKS, 10 AS TT FROM DUAL
UNION ALL   SELECT 'TEAM_A' AS TEAM, 'JOB_3' AS WORKS, 20 AS TT FROM DUAL
UNION ALL   SELECT 'TEAM_B' AS TEAM, 'JOB_2' AS WORKS, 30 AS TT FROM DUAL
)
SELECT
        BASE.TEAM
    ,   BASE.WORKS
    ,   BASE.TT
FROM BASE
FULL OUTER JOIN (
                SELECT 'JOB_1' AS WORKS FROM DUAL
    UNION ALL   SELECT 'JOB_2' AS WORKS FROM DUAL
    UNION ALL   SELECT 'JOB_3' AS WORKS FROM DUAL
) WORK_TYPES ON BASE.WORKS = WORK_TYPES.WORKS
;

Answer 1

You can cross join the distinct teams and works, then bring the table with a left join:

select te.team, wo.work, ta.tt
from (select distinct team from mytable) te
cross join (select distinct work from mytable) wo
left join mytable ta
    on ta.team = te.team and ta.work = wo.work
order by te.team, wo.work