MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Student(
std_id INT,
std_name varchar(2),
key `std_id`(`std_id`) );

insert into Student values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'),
(6,'F');

CREATE TABLE Fee(
fid INT,
std_id int,
amount int,
Month varchar(10),
key std_month(`std_id`,`Month`));

insert into Fee values
(1,1,500,'2022-07'),
(2,2,500,'2022-07'),
(3,4,500,'2022-07'),
(4,1,500,'2022-08'),
(5,2,500,'2022-08');



select s.std_name
from Student s 
where not  exists (select 1 from Fee f where s.std_id=f.std_id and f.Month='2022-07');


std_name


C

E

F


select s.std_name
from Student s 
left join Fee f on s.std_id=f.std_id and f.Month='2022-07' 
where f.Month is null ;


std_name


C

E

F


explain select s.std_name
from Student s 
where not  exists (select 1 from Fee f where s.std_id=f.std_id and f.Month='2022-07');


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
s
null
ALL
null
null
null
null
6
100.00
null

1
SIMPLE
f
null
ref
std_month
std_month
48
db_1863632305.s.std_id,const
1
100.00
Using where; Not exists; Using index


explain select s.std_name
from Student s 
left join Fee f on s.std_id=f.std_id and f.Month='2022-07' 
where f.Month is null ;


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
s
null
ALL
null
null
null
null
6
100.00
null

1
SIMPLE
f
null
ref
std_month
std_month
48
db_1863632305.s.std_id,const
1
100.00
Using where; Using index

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	s	null	ALL	null	null	null	null	6	100.00	null
1	SIMPLE	f	null	ref	std_month	std_month	48	db_1863632305.s.std_id,const	1	100.00	Using where; Not exists; Using index