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 table_1 (id, email_address, sent_status) AS
SELECT 1, 'alice@exmaple.com', 0 FROM DUAL UNION ALL
SELECT 2, 'beryl@exmaple.com', 0 FROM DUAL UNION ALL
SELECT 3, 'carol@exmaple.com', 0 FROM DUAL UNION ALL
SELECT 4, 'debra@exmaple.com', 0 FROM DUAL UNION ALL
SELECT 5, 'emily@exmaple.com', 1 FROM DUAL UNION ALL
SELECT 7, 'fiona@exmaple.com', 0 FROM DUAL UNION ALL
SELECT 8, 'grace@exmaple.com', 0 FROM DUAL;
7 rows affected

CREATE TABLE table_2 (id, color, unused_status) AS
SELECT 100, 'red',    1 FROM DUAL UNION ALL
SELECT 200, 'orange', 1 FROM DUAL UNION ALL
SELECT 300, 'yellow', 0 FROM DUAL UNION ALL
SELECT 400, 'green',  0 FROM DUAL UNION ALL
SELECT 500, 'blue',   0 FROM DUAL UNION ALL
SELECT 700, 'indigo', 0 FROM DUAL UNION ALL
SELECT 800, 'violet', 0 FROM DUAL UNION ALL
SELECT 900, 'black',  0 FROM DUAL;
8 rows affected

SELECT t1.id,
       t1.email_address,
       t2.color
FROM   (
         SELECT id,
                email_address,
                ROW_NUMBER() OVER (ORDER BY id) AS rn
         FROM   table_1
         WHERE  sent_status = 0
       ) t1
       FULL OUTER JOIN (
         SELECT color,
                ROW_NUMBER() OVER (ORDER BY id) AS rn
         FROM   table_2
         WHERE  unused_status = 0
       ) t2
       ON (t1.rn = t2.rn)
ORDER BY COALESCE(t1.rn, t2.rn)
FETCH FIRST 5 ROWS ONLY;


ID
EMAIL_ADDRESS
COLOR


1
alice@exmaple.com
yellow

2
beryl@exmaple.com
green

3
carol@exmaple.com
blue

4
debra@exmaple.com
indigo

7
fiona@exmaple.com
violet

ID	EMAIL_ADDRESS	COLOR
1	alice@exmaple.com	yellow
2	beryl@exmaple.com	green
3	carol@exmaple.com	blue
4	debra@exmaple.com	indigo
7	fiona@exmaple.com	violet