MySQL Query – Fetch IDs Not Available in Table Within Range

MySQLphpmyadminquery

I have table named Student. It have primary key student_id which is auto increment and not null.

Now Student contains around 5000 records.

After passed out from university, we need to delete specific student record from Student and related child table as well.

It is working good.

I need to get those numbers who are not in student_id range.

For ex.

I have 5000 records. So assume range is 1-5000. In Student table, there may be data with primary key :

student_id :

1
2
4
5
6
7
9
12
15
etc…

So I want deleted ids, like : 3, 8, 10, 11, 13, 14, etc… And need this result in some text file.

Finally I have 2 questions :

  • Need query to get those deleted ids
  • Can I export result in some file after fired query ?

Best Answer

SELECT n
FROM (
        SELECT 1000*n1+100*n2+10*n3+n4+1 n
        FROM 
                (
                SELECT 0 n1 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9 
                ) n1,
                (
                SELECT 0 n2 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9
                ) n2,
                (
                SELECT 0 n3 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9
                ) n3,
                (
                SELECT 0 n4 UNION 
                SELECT 1 UNION 
                SELECT 2 UNION 
                SELECT 3 UNION 
                SELECT 4 UNION 
                SELECT 5 UNION 
                SELECT 6 UNION 
                SELECT 7 UNION 
                SELECT 8 UNION 
                SELECT 9
                ) n4
        HAVING n < (
                    SELECT MAX(student_id)
                    FROM Student
                   )
     ) numbers
LEFT JOIN Student ON student_id = n
WHERE student_id IS NULL

fiddle

Related Question