SQL Exercise -18

SQL Exercise:

You are given a table Numbers that contains a sequence of numbers starting from 1 to 10. But some numbers are missing. Your task is to identify the missing numbers using SQL.

Create table script:

CREATE TABLE Numbers (
Num INT
);

Data insertion script:

INSERT INTO Numbers (Num) VALUES 
(1), (2), (3), (4), (6), (7), (9), (10);

Solution:

WITH GenNumbers AS (
SELECT 1 AS ExpectedNum
UNION ALL
SELECT ExpectedNum + 1
FROM GenNumbers
WHERE ExpectedNum < 10
)
SELECT ExpectedNum AS MissingNumber
FROM GenNumbers
LEFT JOIN Numbers ON GenNumbers.ExpectedNum = Numbers.Num
WHERE Numbers.Num IS NULL;

Output:

Explanation:

1. Recursive CTE:
   • The WITH GenNumbers clause starts with an anchor member SELECT 1 AS ExpectedNum, which generates the first number 1.
   • The recursive part SELECT ExpectedNum + 1 FROM GenNumbers increments the number by 1 and continues the recursion as long as ExpectedNum is less than 10.
2. Base Case:
   • The base case is when ExpectedNum = 1.
3. Recursive Step:
   • The recursive part adds 1 to the current ExpectedNum until it reaches 10.
4. LEFT JOIN:
   • After generating the sequence, we LEFT JOIN the GenNumbers CTE with the Numbers table to find the missing number.
5. Filtering Missing Number:
   • The WHERE Numbers.Num IS NULL condition filters out the numbers that are missing in the Numbers table.