force query to return duplicate values sql oracle

Question

i have a simple query

SELECT table1.no,table1.surname,table2.gender FROM table1 JOIN table2 on table1.no= table2.no WHERE table1.no in ('123','456','789','123')

so when this query runs and returns output it only shows 3 rows

table1.no table1.surname table2.gender
123             sss             m
456             aaa             f
789             qqq             m  

but i want the output to be repeated like below

table1.no table1.surname table2.gender
123             sss             m
456             aaa             f
789             qqq             m 
123             sss             m

is there a way i can achieve this

Answer 1

I think the default join type in Oracle is an INNER JOIN, which will remove duplicates. Try switching the JOIN to a LEFT JOIN:

SELECT table1.no,table1.surname,table2.gender FROM table1 LEFT JOIN table2 on table1.no= table2.no WHERE table1.no in ('123','456','789','123')