How can I find unoccupied id numbers in a table?

Question

In my table I want to see a list of unoccupied id numbers in a certain range.

For example there are 10 records in my table with id's: "2,3,4,5,10,12,16,18,21,22" and say that I want to see available ones between 1 and 25. So I want to see a list like:

1,6,7,89,11,13,14,15,17,19,20,23,24,25

How should I write my sql query?

possible duplicate of [SQL query to find Missing sequence numbers](http://stackoverflow.com/questions/1057389/sql-query-to-find-missing-sequence-numbers) — Justin Howard, Nov 26 '14 at 08:12

score 2 · Accepted Answer · answered Nov 26 '14 at 08:13

2

Select the numbers form 1 to 25 and show only those that are not in your table

select n from
( select rownum n from dual connect by level <= 25)
where n not in (select id from table);

answered Nov 26 '14 at 08:13

overflowed

1,773
10
13

This can't be the correct answer as it will show the unmatched numbers in different rows. – Lalit Kumar B Nov 26 '14 at 08:32

score 0 · Answer 2 · answered Nov 26 '14 at 08:13

0

Let's say you a #numbers table with three numbers -

CREATE TABLE #numbers (num INT)

INSERT INTO #numbers (num)
SELECT 1
UNION
SELECT 3
UNION
SELECT 6

Now, you can use CTE to generate numbers recursively from 1-25 and deselect those which are in your #numbers table in the WHERE clause -

;WITH n(n) AS
(
    SELECT 1
    UNION ALL
    SELECT n+1 FROM n WHERE n < 25
)
SELECT n FROM n 
WHERE n NOT IN (select num from #numbers)
ORDER BY n
OPTION (MAXRECURSION 25);

answered Nov 26 '14 at 08:13

Sam

4,302
12
40
74

That doesn't look like valid Oracle SQL. – Mat Nov 26 '14 at 08:16
MAXRECURSION is delimited by 32767 – tungula Nov 26 '14 at 08:16
This is transact-SQL. – Sam Nov 26 '14 at 08:16
@alekotargamadze True, if the numbers are from a smaller set, we can use this. Also, you can easily modify the CTE logic as per your needs. – Sam Nov 26 '14 at 08:22
Well please don't answer questions tagged Oracle with SQL Server-specific code. (Same the other way around of course.) Either answer with portable SQL, or with the right variety for the RDBMS in the question. – Mat Nov 26 '14 at 08:26
Also, it will not aggregate the numbers in output into single row. You will get different rows. – Lalit Kumar B Nov 26 '14 at 08:33

score 0 · Answer 3 · edited May 23 '17 at 12:20

0

You can try using the "NOT IN" clause:

select
    u1.user_id + 1 as start
from users as u1
  left outer join users as u2 on u1.user_id + 1 = u2.id
where
    u2.id is null

see also SQL query to find Missing sequence numbers

edited May 23 '17 at 12:20

Community

1
1

answered Nov 26 '14 at 08:17

StackTrace

9,190
36
114
202

score 0 · Answer 4 · answered Nov 26 '14 at 08:31

You need LISTAGG to get the output in a single row.

SQL> WITH DATA1 AS(
  2  SELECT LEVEL rn FROM dual CONNECT BY LEVEL <=25
  3  ),
  4  data2 AS(
  5  SELECT 2 num FROM dual UNION ALL
  6  SELECT 3 FROM dual UNION ALL
  7  SELECT 4 from dual union all
  8  SELECT 5 FROM dual UNION ALL
  9  SELECT 10 FROM dual UNION ALL
 10  SELECT 12 from dual union all
 11  SELECT 16 from dual union all
 12  SELECT 18 FROM dual UNION ALL
 13  SELECT 21 FROM dual UNION ALL
 14  SELECT 22 FROM dual)
 15  SELECT listagg(rn, ',')
 16  WITHIN GROUP (ORDER BY rn) num_list FROM data1
 17  WHERE rn NOT IN(SELECT num FROM data2)
 18  /

NUM_LIST
----------------------------------------------------
1,6,7,8,9,11,13,14,15,17,19,20,23,24,25

SQL>

How can I find unoccupied id numbers in a table?

4 Answers4