Как правильно сделать JOIN

SELECT 
  match_id, 
  radiant_team, 
  teams.name AS dire_team 
FROM 
  (
    SELECT 
      match_id, 
      teams.name AS radiant_team, 
      dire_team_id 
    FROM 
      matches 
      INNER JOIN teams ON matches.radiant_team_id = teams.team_id 
    WHERE 
      radiant_team_id = (
        SELECT 
          team_id 
        FROM 
          teams 
        WHERE 
          name = 'Aggressive Mode.'
      ) 
      OR dire_team_id = (
        SELECT 
          team_id 
        FROM 
          teams 
        WHERE 
          name = 'Aggressive Mode.'
      )
  ) AS temp_table 
INNER JOIN teams
  ON temp_table.dire_team_id = teams.team_id

WITH 
    team_id AS (
        SELECT
            team_id
        FROM
            teams
        WHERE
            name = 'Aggressive Mode.'
    ),
    
    temp_table AS (
        SELECT
            match_id,
            teams.name AS radiant_team,
            dire_team_id
        FROM
            matches
        INNER JOIN teams
            ON radiant_team_id = teams.team_id
        WHERE
            radiant_team_id = (SELECT * FROM team_id) OR
            dire_team_id = (SELECT * FROM team_id)
    )
    
SELECT
    match_id,
    radiant_team,
    teams.name AS dire_team
FROM
    temp_table
INNER JOIN teams
    ON dire_team_id = teams.team_id

SELECT 
  match_id, 
  tr.name AS radiant_team, 
  td.name AS dire_team 
FROM 
  matches AS m
  LEFT JOIN teams AS tr ON m.radiant_team_id = tr.team_id
  LEFT JOIN teams AS td ON m.dire_team_id = td.team_id
WHERE
  'Aggressive Mode.' IN (tr.name, td.name)