How to force returning multiple lines in SQL when there are duplicates inside "in" clause-CodePudding

My need may seems kind of odd but I need to extract a full column from an excel file and paste it inside a "IN" clause. In this column there are many duplicate values.

The SQL query would look like something like this :

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
WHERE pack.pack_lib in(
'07655_23687_30863',
'07655_23687_30863',
'07432_76544_67890'
)
ORDER BY pack.date_publication DESC;

As you can see in this example, there are 2 duplicate values in the "IN" clause, and the standard behaviour of this query is to return a single value for these 2 values.

BUT I need to get two lines (which will be the same) and not a single one.

The above is just an example, but there wll be many values, some duplicates, and other not duplicates.

How to achieve this ? I work with ORACLE on version 9.2.0.8.0

CodePudding user response：

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]

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

CodePudding user response：

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;