Oracle 21c

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

CREATE TABLE table1 (no, surname) AS
SELECT 123, 'm' FROM DUAL UNION ALL
SELECT 456, 'f' FROM DUAL UNION ALL
SELECT 789, 'm' FROM DUAL;
3 rows affected

CREATE TABLE table2 (no, gender) AS
SELECT 123, 'm' FROM DUAL UNION ALL
SELECT 456, 'f' FROM DUAL UNION ALL
SELECT 789, 'm' FROM DUAL;
3 rows affected

SELECT t1.no,
       t1.surname,
       t2.gender
FROM   table1 t1
       INNER JOIN table2 t2
       ON (t1.no = t2.no)
       INNER JOIN TABLE(SYS.ODCINUMBERLIST(123,456,789,123)) l
       ON (t1.no = l.COLUMN_VALUE);


NO
SURNAME
GENDER


123
m
m

456
f
f

789
m
m

123
m
m