(+91) 7 999 01 02 03

Find the Missing IDs

Ayush Dixit
15 Posts
 Suraz has given us a Students table in which student_id is the primary key for this table. 
Each row of this table contains the name and the id of the student.But Suarz has found out some problem in this table as some of the
student_ids are missing in the table,Suraz has finally decided to assign this task to one of his associate to find out all the missing student_ids.

Table: Students +---------------+---------+ | Column Name | Type | +---------------+---------+ | student_id | int | | student_name | varchar | +---------------+---------+ Write an SQL query to find the missing student IDs.
The missing IDs are ones that are not in the Customers table but are in the range between 1 and the maximum customer_id present in the table. Notice that the maximum customer_id will not exceed 100. Return the result table ordered by ids in ascending order. The query result format is in the following example. Student table: +-------------+---------------+ | student_id | student_name | +-------------+---------------+ | 1 | Alice | | 4 | Bob | | 5 | Charlie | +-------------+---------------+ Result table: +-----+ | ids | +-----+ | 2 | | 3 | +-----+ The maximum customer_id present in the table is 5, so in the range [1,5], IDs 2 and 3 are missing from the table.

My Solution:-

There are many ways to solve this problem,but in this article I am only going to cover the most easiest way to solve this problem,
and my approch will help you to understand the concept of RECURSICE CTE in deep.

In my solution I'm going to generate the Ids starting from 1 to maximum of student_id with the help of
after this I will use this CTE output to find the missing IDs.

Final Query:-

SELECT 1 AS 'id', MAX(s.student_id) AS 'Max_Id'
FROM Students s


SELECT id+1, Max_Id
WHERE id < Max_id

SELECT id AS 'ids'
WHERE NOT IN (SELECT student_id FROM Students)

Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.

Published By : Ayush Dixit
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.