MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TEAMS AS
  SELECT 10 AS TEAM_ID, 'Give' AS TEAM_NAME UNION ALL
  SELECT 20, 'Never' UNION ALL
  SELECT 30, 'You' UNION ALL
  SELECT 40, 'Up' UNION ALL
  SELECT 50, 'Gonna';

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM TEAMS


TEAM_ID
TEAM_NAME


10
Give

20
Never

30
You

40
Up

50
Gonna


CREATE TABLE MATCHES AS
SELECT 1 AS MATCH_ID, 30 AS HOST_TEAM, 20 AS GUEST_TEAM, 1 AS HOST_GOALS, 0 AS GUEST_GOALS UNION ALL 
SELECT 2, 10, 20, 1, 2 UNION ALL
SELECT 3, 20, 50, 2, 2 UNION ALL
SELECT 4, 10, 30, 1, 0 UNION ALL
SELECT 5, 30, 50, 0, 1;

Records: 5  Duplicates: 0  Warnings: 0


SELECT * FROM MATCHES


MATCH_ID
HOST_TEAM
GUEST_TEAM
HOST_GOALS
GUEST_GOALS


1
30
20
1
0

2
10
20
1
2

3
20
50
2
2

4
10
30
1
0

5
30
50
0
1


WITH cteHostPoints AS (SELECT HOST_TEAM AS TEAM,
                              CASE
                                WHEN HOST_GOALS > GUEST_GOALS THEN 3
                                WHEN HOST_GOALS = GUEST_GOALS THEN 1
                                ELSE 0
                              END AS POINTS
                         FROM MATCHES),
     cteGuestPoints AS (SELECT GUEST_TEAM AS TEAM,
                               CASE
                                 WHEN GUEST_GOALS > HOST_GOALS THEN 3
                                 WHEN GUEST_GOALS = HOST_GOALS THEN 1
                                 ELSE 0
                               END AS POINTS
                          FROM MATCHES),
     cteAllPoints AS (SELECT TEAM, POINTS FROM cteHostPoints
                      UNION ALL
                      SELECT TEAM, POINTS FROM cteGuestPoints)
SELECT t.TEAM_ID, t.TEAM_NAME, COALESCE(SUM(ap.POINTS), 0) AS TOTAL_POINTS
  FROM TEAMS t
  LEFT OUTER JOIN cteAllPoints ap
    ON ap.TEAM = t.TEAM_ID
  GROUP BY t.TEAM_ID, t.TEAM_NAME
  ORDER BY COALESCE(SUM(POINTS), 0) DESC, t.TEAM_ID


TEAM_ID
TEAM_NAME
TOTAL_POINTS


20
Never
4

50
Gonna
4

10
Give
3

30
You
3

40
Up
0