CODEWITHSUNDEEP
sqldatabaseoracle-database
user9892866
user9892866

Reputation:

missing values in sql

I have table EMP which has only 1 column id

I have below input

1
3
4
6
10

I want output

2
5
7
8
9

I have written the below query

select level from dual connect by level<=10 
minus 
select id from emp;

Is there any other way to write this query? Can this be written without using connect by

Upvotes: 1

Views: 137

Answers (4)

Maxim Borunov
Maxim Borunov

Reputation: 911

Public system varray sys.odcinumberlist:

select column_value
from   table(sys.odcinumberlist(1,2,3,4,5,6,7,8,9,10))
minus
select id from emp

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If you can take the results in a different form, then you can write this using lead():

select (id + 1) as first_missing_id,
       (next_id - 1) as last_missing_id,
       (next_id - id - 1) as num_missing
from (select e.*, lead(e.id) over (order by e.id) as next_id
      from emp e
     ) e
where next_id <> id + 1;

This shows the missing ranges of ids. It is also probably faster than any version that creates a separate row for each id.

Upvotes: 0

Super Mario
Super Mario

Reputation: 939

Tricky one:

select rownum as id
from ALL_OBJECTS --(OR OTHER BIG TABLE)
where rownum <= 5    
minus
select id from emp;

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27261

Is there any other way to write this query. Can this be written without using connect by

Yes it can be written without connect by. The same thing can be done with CTE(it requires you some typing to do, but it's more generic in terms of code portability), which in this case does the same thing - generates a sequence of numbers. The idea is pretty much the same, different wording.

-- CTE. generates numbers from 1 to 10
with numbers(digit) as(
  select 1 from dual
  union all
  select digit + 1
    from numbers
   where digit < 10
)
select digit from numbers
minus 
select id from emp;

Upvotes: 1

Related Questions