Home > Net >  force query to return duplicate values sql oracle
force query to return duplicate values sql oracle

Time:02-18

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

db<>fiddle here

  • Related