How can the SQL query for counting home wins and away wins be optimized to avoid issues with UNION?

To avoid using UNION in the SQL query for counting home wins and away wins, you can use a CASE statement within the SELECT clause to differentiate between home and away wins. This way, you can count the wins separately without the need for UNION, which can improve query performance. 

$sql = "SELECT 
            SUM(CASE WHEN home_team = 'Team A' AND home_score > away_score THEN 1 ELSE 0 END) AS home_wins,
            SUM(CASE WHEN away_team = 'Team A' AND away_score > home_score THEN 1 ELSE 0 END) AS away_wins
        FROM matches
        WHERE home_team = 'Team A' OR away_team = 'Team A'";

Keywords

SQL query optimization home wins away wins UNION

Related Questions