How to force returning multiple lines in SQL when there are duplicates inside "in" clause (Oracle)

Solution 1:

In Oracle, you can INNER JOIN to a table collection expression:

SELECT pack.pack_lib,
       upub.usr_nni,
       upub.usr_email
FROM   t_package pack
       LEFT OUTER JOIN t_user upub
         ON upub.usr_id = pack.usr_id_publication
       INNER JOIN TABLE(SYS.ODCIVARCHAR2LIST(
         '07655_23687_30863',
         '07655_23687_30863',
         '07432_76544_67890'
       )) l
         ON pack.pack_lib = l.COLUMN_VALUE
ORDER BY pack.date_publication DESC;

Which, for the sample data:

CREATE TABLE t_user(usr_id, usr_nni, usr_email) AS
SELECT 1, 1, '[email protected]' FROM DUAL UNION ALL
SELECT 2, 2, '[email protected]' FROM DUAL;

CREATE TABLE t_package(usr_id_publication, pack_lib, date_publication) AS
SELECT 1, '07655_23687_30863', DATE '2022-01-01' FROM DUAL UNION ALL
SELECT 2, '07432_76544_67890', DATE '2022-01-02' FROM DUAL;

Outputs:

PACK_LIB	USR_NNI	USR_EMAIL
07432_76544_67890	2	[email protected]
07655_23687_30863	1	[email protected]
07655_23687_30863	1	[email protected]

If you do not have visibility of the SYS.ODCI*LIST types then you may be able to create a user-defined collection type:

CREATE TYPE string_list AS TABLE OF VARCHAR2(20);

then use:

SELECT pack.pack_lib,
       upub.usr_nni,
       upub.usr_email
FROM   t_package pack
       LEFT OUTER JOIN t_user upub
         ON upub.usr_id = pack.usr_id_publication
       INNER JOIN TABLE(string_list(
         '07655_23687_30863',
         '07655_23687_30863',
         '07432_76544_67890'
       )) l
         ON pack.pack_lib = l.COLUMN_VALUE
ORDER BY pack.date_publication DESC;

db<>fiddle here

Solution 2:

You need to use JOIN instead of semi-join (the IN syntax). The following query should work on Oracle:

SELECT pack.pack_lib,
  upub.usr_nni,
  upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub ON upub.usr_id = pack.usr_id_publication
LEFT OUTER JOIN (
    SELECT '07655_23687_30863' pack_lib FROM dual
    UNION ALL
    SELECT '07655_23687_30863' FROM dual
) t
  ON pack.pack_lib = t.pack_lib; 

SQL Server and PostgreSQL should allow also the following syntax

SELECT pack.pack_lib,
  upub.usr_nni,
  upub.usr_email
FROM t_package pack
LEFT OUTER JOIN t_user upub ON upub.usr_id = pack.usr_id_publication
LEFT OUTER JOIN (VALUES ('07655_23687_30863'), ('07655_23687_30863')) AS t(pack_lib)   
  ON pack.pack_lib = t.pack_lib;