force query to return duplicate values sql oracle-CodePudding

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

CodePudding user response：

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')

CodePudding user response：

Pass in a collection rather than using an IN filter:

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);

Which, for the sample data:

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;

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;

Outputs:

NO SURNAME GENDER

123 m m

456 f f

789 m m

123 m m

NO	SURNAME	GENDER
123	m	m
456	f	f
789	m	m
123	m	m

db<>fiddle here