Nested Query equivalent to inner join-CodePudding

I'm trying to better understand nested queries, and I was wondering what would get me the same result as this:

SELECT PLAYERS.PLAYER_TAG, PLAYERS.POSITION, PLAYERS.TOTAL_KILLS, TEAMS.TEAM_NAMES, TEAMS.GAMES_PLAYED, TEAMS.WINS
FROM PLAYERS
INNER JOIN TEAMS ON TEAMS.TEAM_NAMES = PLAYERS.TEAM
ORDER BY TEAM_NAMES ASC;

but using a nested query instead. Thanks for your help

CodePudding user response：

This is not that kind of query when you need any nested ones (sub-queries) due to lack of any predicates (WHERE clause).

CodePudding user response：

You would not use this and would just use the INNER JOIN query.

However, if a player will only ever be on one team then this query:

SELECT PLAYER_TAG,
       POSITION, 
       TOTAL_KILLS,
       ( SELECT TEAM_NAMES
         FROM   teams
         WHERE  TEAMS.TEAM_NAMES = PLAYERS.TEAM ) AS teams_names,
       ( SELECT GAMES_PLAYED
         FROM   teams
         WHERE  TEAMS.TEAM_NAMES = PLAYERS.TEAM ) AS games_played,
       ( SELECT WINS
         FROM   teams
         WHERE  TEAMS.TEAM_NAMES = PLAYERS.TEAM ) AS wins
FROM   PLAYERS
ORDER BY TEAM_NAMES ASC;

(If a player can be on multiple teams then the above query will raise an exception as the nested sub-queries return more than a single row.)

Would be the (inefficient) equivalent of:

SELECT PLAYERS.PLAYER_TAG,
       PLAYERS.POSITION,
       PLAYERS.TOTAL_KILLS,
       TEAMS.TEAM_NAMES,
       TEAMS.GAMES_PLAYED,
       TEAMS.WINS
FROM   PLAYERS
       LEFT OUTER JOIN TEAMS
       ON TEAMS.TEAM_NAMES = PLAYERS.TEAM
ORDER BY TEAM_NAMES ASC;

(Using a LEFT OUTER JOIN rather than an INNER JOIN.)

If you wanted an INNER JOIN then you can add an EXISTS filter to the outer query's WHERE clause.