mysql – Why the output is wrong after the left join was executed as a subquery

I have TWO QUESTIONS they work perfectly, but when I query the two as join CONNECT LINKS The output will be wrong. So what's the problem?

Query 1:

Select Batsman.innings_no.
Bowler,
Sum (balls) as B,
ifnull (sum (Runs_In_Over), 0) as R,
ifnull (sum (zero), 0) as 0s,
ifnull (sum (four), 0) as 4s,
ifnull (sum (six), 0) as 6s
from (SELECT A.innings_no,
A.bowler,
counting (*) as balls,
Sum (B.Runs_Scored) As & # 39; Runs_In_Over & # 39 ;,
Case B.runs_scored
if 0, then count (*) as zero,
Case B.runs_scored
If 4, then count (*) as four,
Case B.runs_scored
If 6, then count (*) as six
FROM `database`.ball_by_ball A
INNER JOIN `database`.batsman_scored B
using (match_id, over_id, ball_id, innings_no)
Where match_id = 981018
and innings_no = 2
GROUP BY A.innings_no, A.bowler, B.runs_scored) as a batsman
group by innings_no, bowler order by bowler;

Query 2:

choose innings_no,
Bowler,
ifnull (sum (Wides), 0) as WD,
ifnull (sum (NoBalls), 0) as NB,
sum (extra_runs) as an extra
from (SELECT D.innings_no,
D.bowler,
case E.extra_type_id
if 2 then counts (*) as ends,
case E.extra_type_id
if 4 ends (*) as NoBalls,
Sum (E.Extra_Runs) as & # 39; Extra_runs & # 39;
FROM `database`.ball_by_ball D
INNER JOIN `database`.extra_runs E
using (match_id, over_id, ball_id, innings_no)
WHERE match_id = 981018
and innings_no = 2
and E.Extra_Type_Id IN (2, 4)
GROUP BY D.innings_no, D.bowler, E.extra_type_id) as an extra
group by innings_no, bowler order by bowler;

Join Query:

Select Batsman.innings_no.
Batsman.bowler,
Sum (balls) as B,
ifnull (sum (Runs_In_Over), 0) as R,
ifnull (sum (zero), 0) as 0s,
ifnull (sum (four), 0) as 4s,
ifnull (sum (six), 0) as 6s,
ifnull (sum (Wides), 0) as WD,
ifnull (sum (NoBalls), 0) as NB,
ifnull (sum (extra_runs), 0) as an extra
from (SELECT A.innings_no,
A.bowler,
counting (*) as balls,
Sum (B.Runs_Scored) As & # 39; Runs_In_Over & # 39 ;,
Case B.runs_scored
if 0, then count (*) as zero,
Case B.runs_scored
If 4, then count (*) as four,
Case B.runs_scored
If 6, then count (*) as six
FROM `database`.ball_by_ball A
INNER JOIN `database`.batsman_scored B
using (match_id, over_id, ball_id, innings_no)
Where match_id = 981018
and innings_no = 2
GROUP BY A.innings_no, A.bowler, B.runs_scored) as a batsman
LEFT JOIN (SELECT D.innings_no,
D.bowler,
case E.extra_type_id
if 2 then counts (*) as ends,
case E.extra_type_id
if 4 ends (*) as NoBalls,
Sum (E.Extra_Runs) as & # 39; Extra_runs & # 39;
FROM `database`.ball_by_ball D
INNER JOIN `database`.extra_runs E
using (match_id, over_id, ball_id, innings_no)
WHERE match_id = 981018
and innings_no = 2
and E.Extra_Type_Id IN (2, 4)
GROUP BY D.innings_no, D.bowler, E.extra_type_id) as extra using (innings_no, bowler)
group by innings_no, bowler order by bowler;

Query 1 output:

Enter the image description here

Query 2 output:

Enter the image description here

Join Query Output:

Enter the image description here

If you've seen screenshot 1 and 2, compare it to the join query bowlerThen it's clear that when I use Left Join as a subquery, the result does not exactly show what's displayed in a single query.

So what's the problem? What do I have to do to solve this problem and why does this issue occur?