How to get ids which does not exist in another listagg

I have written a query to get values in comma separated format from both the table

Table 1 :

SELECT regex_replace( xmlcast(Xmlagg(XMLELEMENT(empid,empid,',')) as clob),'\s*,\s*$','') AS str1
FROM   (
              SELECT empid
              FROM   employee );

Table 2 :

SELECT regex_replace( xmlcast(Xmlagg(XMLELEMENT(depid,depid,',')) as clob),'\s*,\s*$','') AS str2
FROM   (
              SELECT depid
              FROM   department);

Output of both quries:

str1 = 1,4,5,6,8
str2 = 1,5,6

How do I compare both the str1 and str2 and get ids which are in str1 but not in str2

Expected output : 4,8

CodePudding user response：

You do not need to compare the delimited strings, you can simply use NOT IN (or NOT EXISTS) and compare the table values:

SELECT regexp_replace( xmlcast(Xmlagg(XMLELEMENT(empid,empid,',')) as clob),'\s*,\s*$','') AS str1
FROM   employee
WHERE  empid NOT IN (
         SELECT depid
         FROM   department
       );

However, you should consider whether it makes sense to compare the IDs for employees to the IDs for departments as that does not appear to make logical sense.

For the sample data:

CREATE TABLE employee (empid) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 4 FROM DUAL UNION ALL
SELECT 5 FROM DUAL UNION ALL
SELECT 6 FROM DUAL UNION ALL
SELECT 8 FROM DUAL;

CREATE TABLE department (depid) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 5 FROM DUAL UNION ALL
SELECT 6 FROM DUAL;

The query outputs:

STR1

4,8

STR1
4,8

db<>fiddle here

CodePudding user response：

Another approach considering that both str1 and str2 are coming from different tables. Although I consider the comments more than right that this kind of comparison should not be done this way.

with x as 
(
select regexp_substr(x.str1,'[^,] ',1,level) as str1_spit 
from ( select '1,4,5,6,8' as str1 from dual ) x 
CONNECT BY LEVEL <=REGEXP_COUNT(x.str1 ,'[,]')   1 
), 
y as 
( select regexp_substr(y.str2,'[^,] ',1,level) as str2_spit 
from ( select '1,5,6' as str2 from dual ) y 
CONNECT BY LEVEL <=REGEXP_COUNT(y.str2 ,'[,]')   1 
)
select LISTAGG(str1_spit, ',') WITHIN GROUP (order by str1_spit) as final_value 
from 
(
select x.str1_spit , y.str2_spit 
from x left join y on x.str1_spit = y.str2_spit 
where y.str2_spit is null
order by x.str1_spit 
)

Demo

SQL> with x as
  2  (
select regexp_substr(x.str1,'[^,] ',1,level) as str1_spit
from ( select '1,4,5,6,8' as str1 from dual ) x
CONNECT BY LEVEL <=REGEXP_COUNT(x.str1 ,'[,]')   1
),
y as
( select regexp_substr(y.str2,'[^,] ',1,level) as str2_spit
from ( select '1,5,6' as str2 from dual ) y
CONNECT BY LEVEL <=REGEXP_COUNT(y.str2 ,'[,]')   1
)
select LISTAGG(str1_spit, ',') WITHIN GROUP (order by str1_spit) as final_value
from
(
select x.str1_spit , y.str2_spit
from x left join y on x.str1_spit = y.str2_spit
where y.str2_spit is null
order by x.str1_spit
)  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19  ;

FINAL_VALUE
--------------------------------------------------------------------------------
4,8

SQL>

CodePudding user response：

If you must do it by comparing strings (don't, use NOT IN or NOT EXISTS and compare the tables) then you can do it by only splitting one of the two strings and using simple string functions (rather than regular expressions, which are an order-of-magnitude slower):

WITH data (str1, str2) AS (
  SELECT TO_CLOB('1,4,5,6,8'),
         TO_CLOB('1,5,6')
  FROM   DUAL
),
bounds (str1, str2, spos, epos) AS (
  SELECT str1,
         str2,
         1,
         INSTR(str1, ',', 1)
  FROM   data
UNION ALL
  SELECT str1,
         str2,
         epos   1,
         INSTR(str1, ',', epos   1)
  FROM   bounds
  WHERE  epos > 0
),
items (item, str2) AS (
  SELECT CASE epos
         WHEN 0
         THEN SUBSTR(str1, spos)
         ELSE SUBSTR(str1, spos, epos - spos)
         END,
         ',' || str2 || ','
  FROM   bounds
  ORDER BY spos
)
SELECT regexp_replace(
         xmlcast(Xmlagg(XMLELEMENT(item,item,',')) as clob),
         '\s*,\s*$'
       ) AS str3
FROM   items
WHERE  str2 NOT LIKE '%,' || item || ',%';

Which outputs:

STR3

4,8

STR3
4,8

db<>fiddle here