Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE a(id) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;
3 rows affected

CREATE TABLE b(id, var1) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 2, 2 FROM DUAL UNION ALL
SELECT 3, 3 FROM DUAL;
3 rows affected

CREATE TABLE c(var1) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL;
3 rows affected

CREATE TABLE d(var2) AS
SELECT 5 FROM DUAL UNION ALL
SELECT 6 FROM DUAL;
2 rows affected

CREATE TABLE x(id) AS
SELECT 7 FROM DUAL UNION ALL
SELECT 8 FROM DUAL;
2 rows affected

CREATE TABLE y (id) AS
SELECT 7 FROM DUAL UNION ALL
SELECT 7 FROM DUAL;
2 rows affected

SELECT a.ID,
       c.var1,
       d.var2
FROM   a, b, c, d, x, y
WHERE  a.ID(+) = b.ID
AND    c.var1(+) = b.var1
AND    x.id(+) = y.id;


ID
VAR1
VAR2


1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

null
2
5

null
2
6

null
2
5

null
2
6

3
null
5

3
null
6

3
null
5

3
null
6


SELECT a.ID,
       c.var1,
       d.var2
FROM   b
       LEFT OUTER JOIN a ON a.ID = b.ID
       LEFT OUTER JOIN c ON c.var1 = b.var1
       CROSS JOIN d
       CROSS JOIN y
       LEFT OUTER JOIN x ON x.id = y.id;


ID
VAR1
VAR2


1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

1
1
5

1
1
6

null
2
5

null
2
6

null
2
5

null
2
6

3
null
5

3
null
6

3
null
5

3
null
6

ID	VAR1	VAR2
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
null	2	5
null	2	6
null	2	5
null	2	6
3	null	5
3	null	6
3	null	5
3	null	6

ID	VAR1	VAR2
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
1	1	5
1	1	6
null	2	5
null	2	6
null	2	5
null	2	6
3	null	5
3	null	6
3	null	5
3	null	6