Oracle Query to get the records which are not in the results sets-CodePudding

I have a scenario where I need to query the table to see the how many records are not present from the particular record set.

for eg -I have a record set (deptno1 ,deptno2 ....deptno100)

Table - Emp1

I would like to know how many records from that record set are not present in the table emp1.

If I run this query - select * from emp1 where deptno notin(deptno1....deptno100) it gives the records other than 100 records eventhough those 100 records might be in that table

CodePudding user response：

You can use:

WITH data (deptno) AS (
  SELECT 'deptno1' FROM DUAL UNION ALL
  SELECT 'deptno2' FROM DUAL UNION ALL
  -- ...
  SELECT 'deptno100' FROM DUAL
)
SELECT d.deptno
FROM   data d
       LEFT OUTER JOIN emp1 e
       ON (d.deptno = e.deptno)
WHERE  e.deptno IS NULL;

Or, with slightly different syntax that should work on early Oracle versions, either:

WITH data AS (
  SELECT 'deptno1' AS deptno FROM DUAL UNION ALL
  SELECT 'deptno2' FROM DUAL UNION ALL
  -- ...
  SELECT 'deptno100' FROM DUAL
)
SELECT d.deptno
FROM   data d
       LEFT OUTER JOIN emp1 e
       ON (d.deptno = e.deptno)
WHERE  e.deptno IS NULL;

SELECT d.deptno
FROM   (
         SELECT 'deptno1' AS deptno FROM DUAL UNION ALL
         SELECT 'deptno2' FROM DUAL UNION ALL
         -- ...
         SELECT 'deptno100' FROM DUAL
       ) d
       LEFT OUTER JOIN emp1 e
       ON (d.deptno = e.deptno)
WHERE  e.deptno IS NULL;

db<>fiddle

CodePudding user response：

You can generate the values using connect by.

with d(dept) AS ( select 'deptno' || to_char(level) from dual connect by level <= 100 ) select d.dept from d left join emp1 e on (d.dept = e.deptno) where e.deptno is null;