1 Reply Latest reply: Oct 4, 2012 7:53 PM by orafad RSS

    Left Outer Join - 11g

    892619
      I am studying for the 1Z0-051 exam and this question was presented. Could anybody please help me understand why there is team t left outer join ON (p.team_id=t.team_id)? By the problem the possible null value occurs in the Manager_id field, justifying the left outer join between p.manager_id=m.player_id. I realize this is likely a stupid question but joins are really hard for me to get a grasp on for some reason. Any help will be greatly appreciated.
      Examine the structures of the PLAYER and TEAM tables:

      PLAYER
      -------------
      PLAYER_ID NUMBER(9) PK
      LAST_NAME VARCHAR2(25)
      FIRST_NAME VARCHAR2(25)
      TEAM_ID NUMBER
      MANAGER_ID NUMBER(9)

      TEAM
      ----------
      TEAM_ID NUMBER PK
      TEAM_NAME VARCHAR2(30)

      For this example, team managers are also players, and the MANAGER_ID column references the PLAYER_ID column. For players who are managers, MANAGER_ID is NULL.

      Which SELECT statement will provide a list of all players, including the player's name, the team name, and the player's manager's name?
           
           SELECT p.last_name, p.first_name, m.last_name, m.first_name, t.team_name
      FROM player p
      LEFT OUTER JOIN player m ON (p.manager_id = m.player_id)
      LEFT OUTER JOIN team t ON (p.team_id = t.team_id);