SQL Interview Questions and answers Part 24 | How to find missing rows in SQL

SQL Interview Questions and answers Part 24 | How to find missing rows in SQL | SQL Scenario based Interview Question and Answer

Input :- Sample Table has ID column which is not continuous value starting from 1 to 20
Problem Statement :- Write a SQL to find the missing ID From sample Table

Follow me on Social Networking Sites :
*Twitter:
*Instagram :

Table Script
------------------------------
Create Table Sample_Table(
ID int)

Insert into Sample_Table Values (1),(4),(7),(9),(12),(14),(16),(17),(20)

#SQLInterviewQuestionsandanswers #sqlInterviewQuestions #sqlInterviewQuestionsForTesting #sqlInterviewQuestionsQuery #sqlInterviewQuestionsOnJoins #sqlTechnicalInterviewQuestions
#SQLforETLTesters #CommonSqlInterviewQuestions #ETLTesting

[vivekjain]

Best part is you gave create and insert statement, because of this I can do practice

[jeewanmaharjan]

I am preparing Interview for sql developers and your channel seems very useful to me. Cheers

[dipalipatil]

select level from dual connect by level<=(select max(a) from table)
minus
select * from table

[abdulmalik]

We can use the below query to get the output
With CTE_Numbers as
(
Select 1 as Firstvalue
union all
Select Firstvalue+1
from CTE_Numbers
where Firstvalue <20
)
select firstvalue from CTE_Numbers
except
Select id from Sample_Table

[saisanjeevareddy]

with cte as (select *, lead(id) over(order by id) as ld
from sample_table
union all
select id+1, ld
from cte
where id<ld)

select *
from(select id
from cte
group by id
)subq
except
select *
from sample_table

[somasreebiswas]

All your videos have good explanations. instead of inner join we can also use except operator:
select * from mincte except (select * from sample_table)

[sonurawat]

select level as lvl from dual
connect by level<=20
minus
select id from sample_table;

[notavi]

with recursive nums as (
select 1 as n
union all
select n + 1
from nums
where nums.n < 100
),
sample as (
select *, lead(id) over(order by id) next
from Sample_Table
)
select n from nums a
inner join sample b
on a.n > b.id and a.n < b.next
;

[sarvesht]

Can we generate rownum upto 20 and do minus operation with sample table to get the missing values ? Will it work ?

[somasreebiswas]

tried using temp table:

declare @var1 int
set @var1 = 1

create table #tabs (col int)
begin
if @var1 not in (select * from Sample_Table)
insert into #tabs select @var1
set @var1 = @var1+ 1
end;

select * from #tabs;

[anushreeranjan]

I know my solution is not dynamic, but is this solution acceptable? I think your solution is almost similar, only that your's is dynamic, which is better.

with recursive cte as
( select 1 as n
Union all
select n+1 from cte
where n<20)
select * from cte where n not in (select ID from sample_table);

[mayankbisht]

Can you please make a video on this - Write a SQL script not query to find out the missing numbers - 2, 5, 8, 17, 20, 23, 29, 41
ans - 11, 14, 26, 32, 35, 38

[bishwarup]

with recursive base as(
/*Anchor part */
select 1 as counter
UNION ALL
/*Recursive part */
select counter+1 as counter
from base where counter+ 1 <= (select max(ID) from sample_table)
)
select * from base b
where counter not in (select * from sample_table);

[kanchannatekar]

with missing_cte as

( select min(id) as min_id from Missing_ID_Table

union all

select min_id +1 as id from missing_cte where min_id <20)
select * from missing_cte
except
select * from Missing_ID_Table

[notavi]

with recursive nums as (
select 1 as n
union all
select n + 1
from nums
where nums.n < (select max(id) from sample_table)
)
select n from nums
except
select id from sample_table;

[rohitdeshbhratar]

getting same output from this query.

select level from dual connect by level<=20
minus
select id from id ;

[YASHKUMARJAIN]

with recursive cte as (
select 1 as newid
union all
select newid +1 from cte
where newid !=20 )
select newid from cte
where newid not in (select * from Sample_Table )

[prashant]

select * from
(select level as lvl from dual a connect by level<=20) a
where not exists (select 1 from sample_table b where a.lvl=b.id)
order by 1; in oracle

[rajarshimaity]

with msntbl(num) as (
select 1 as num from dual
union all
select num+1 as num from msntbl where num < 20
)
select a.num from msntbl a where a.num not in (select id from sample_table) order by a.num

[vasukenguva]

Select level as id from dual connect by level <=(select max(id) from sample_table)

Minus

Select id from sample_table;