SQL union of two tables with different columns

advertisements

I want to get one result set of rows back from two tables with different column names, one line per line in each table.

Result should look like this, blanks can be null, team_id in 2nd half comes from coach_id:

-----------------------------------------
player_id | team_id | score | improvement
-----------------------------------------
11          20         5
11          21         4
12          22         2
12          23         2
11          20                   5
11          21                   6
12          21                   5
13          23                   10

Here's the schema:

CREATE TABLE coaches
    (`id` int, `team_id` int)
;

INSERT INTO coaches
    (`id`, `team_id`)
VALUES
    (1, 20),
    (2, 21),
    (3, 22),
    (4, 23)
;

CREATE TABLE players
 (`id` int, `player_id` int);

INSERT INTO players
(`id`, `player_id`)
VALUES
(1,11),
(2,12),
(3,13),
(4,14)
;
CREATE TABLE games
    (`id` int, `player_id` int, `team_id` int, `score` int)
;

INSERT INTO games
    (`id`, `player_id`, `team_id`, `score`)
VALUES
    (1, 11, 20, 5),
    (2, 11, 21, 4),
    (3, 12, 22, 2),
    (4, 12, 23, 2)
;

CREATE TABLE sessions
    (`id` int, `player_id` int, `coach_id` int, `improvement` int)
;

INSERT INTO sessions
      (`id`, `player_id`, `coach_id`, `improvement`)
VALUES
    (1, 11, 1, 5),
    (2, 11, 2, 6),
    (3, 12, 2, 5),
    (4, 13, 4, 10)
;

Tried this, but it wasn't really close:

SELECT tweets.player_id
      ,tweets.team_id
      ,follows.coach_id
FROM tweets FULL OUTER JOIN follows ON (1 = 0);


nulnullry this

 SELECT player_id
    ,team_id
    ,score
    ,NULL AS improvement
FROM games
UNION All
SELECT sessions.player_id
    ,coaches.team_id
    ,NULL AS score
    ,sessions.improvement
FROM sessions
INNER JOIN coaches ON coaches.id = sessions.coach_id