Find the Missing IDs

 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 RECURSICE CTE,
 after this I will use this CTE output to find the missing IDs.

 Final Query:-

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

  UNION ALL

  SELECT id+1, Max_Id
  FROM CTE
  WHERE id < Max_id
 )


 SELECT id AS 'ids'
 FROM CTE c
 WHERE c.id NOT IN (SELECT student_id FROM Students)
 ORDER BY 1 ASC



 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.