The JOIN WC2026

Language:Project:Language policy	English

schema:wc2026

We start with just two tables, goal and team. These tables are linked because the team column in goal matches the id column in team.

goal

game	team	player	gtime
1001	MEX	Raúl Alonso Jimenez	67
1001	MEX	Julián Andrés Quinones	9
1002	CZE	Ladislav Krejci	59
1002	KOR	Inbeom Hwang	67
...

team

id	teamname	coach
CZE	Czech Republic	Miroslav Koubek
KOR	South Korea	Hong Myung-bo
MEX	Mexico	Javier Aguirre
...

This tutorial introduces JOIN which allows you to use data from two or more tables. The tables contain all matches and goals from FIFA World Cup 2026 in Canada, USA & Mexico.

The data is available (mysql format) at https://sqlzoo.net/wc2026-mysql.sql

Summary

Leandro Trossard goals

The first example shows the goals scored by a player with the first name 'Harry'.

Modify it to show the game, team, player, gtime for all goals scored by player 'Leandro Trossard'

SELECT game,team,player,gtime
  FROM goal 
 WHERE player LIKE 'Harry%'

SELECT game, team, player , gtime
  FROM goal
 WHERE player = 'Leandro Trossard'

Team names

The team for player Leandro Trossard has code 'BEL'

Show the id, teamname and coach for the team with code 'BEL'

SELECT id, teamname, coach
  FROM team
 WHERE id IN ('CIV', 'CPV', 'CRO')

SELECT id, teamname, coach
  FROM team
 WHERE id = 'BEL'

JOIN

You can combine the two steps into a single query with a JOIN.

SELECT *
  FROM goal JOIN team ON goal.team=team.id

The FROM clause says to combine data from the goal table with that from the team table. The ON says how to figure out which rows in game go with which rows in team - the team from goal must match id from team.

Show the player, gtime and teamname for every goal with goal time (gtime) less than 8 minutes.

SELECT player, team, teamname
  FROM goal JOIN team ON goal.team=team.id
 WHERE gtime < 8

SELECT player, gtime, teamname
  FROM goal JOIN team ON goal.team=team.id
 WHERE gtime < 8

Coach Sébastien

Use the same JOIN as in the previous question.

Show the player, teamname and coach for every goal scored by a team with coach named 'Sébastien'

SELECT *
  FROM team
 WHERE coach LIKE 'Sébastien%'

SELECT player, teamname, coach
  FROM goal JOIN team ON (goal.team=team.id)
WHERE coach LIKE 'Sébastien%'

---

The table game lists both teams, the date played and the city. The column id is referenced by the goal table.

game

id	played	city	team1	team2
1001	2026-06-11	Mexico City	MEX	RSA
1002	2026-06-11	Guadalajara	KOR	CZE
1003	2026-06-12	Toronto	CAN	BIH
1004	2026-06-12	Los Angeles	USA	PAR
...

Where's Harry?

For each goal by 'Harry Edward Kane' show the player, the game id and the city

SELECT played,game.id,player
  FROM goal JOIN game ON goal.game=game.id
 WHERE player = 'Harry Edward Kane'

SELECT player,id,city
  FROM goal JOIN game ON goal.game=game.id
 WHERE player = 'Harry Edward Kane'

Games in Vancouver

List the player and team (short code) for every goal scored in 'Vancouver'

SELECT player, team
FROM goal JOIN game ON goal.game=game.id
WHERE city='Vancouver'

Goal Scorer

You will need to join in another table to answer this question.

List the player and the teamname for every goal scored in 'Vancouver'

SELECT player,teamname
FROM goal JOIN game ON goal.game=game.id JOIN team ON goal.team=team.id
WHERE city='Vancouver'

More difficult questions

Where did England score

You will need to use a GROUP BY expression and the aggregate COUNT(*) here.

For each city where 'ENG' scored- show many goals they scored.

SELECT game,COUNT(*)
  FROM goal
 WHERE team='ENG'
GROUP BY game

SELECT city,COUNT(*)
FROM goal JOIN game ON goal.game=game.id
WHERE team='ENG'
GROUP BY city

Total goals scored

Show teamname and the total number of goals scored.

You should COUNT(*) in the SELECT line and GROUP BY teamname

SELECT teamname,COUNT(*)
 FROM goal JOIN team ON goal.team=team.id
GROUP BY teamname
ORDER BY COUNT(*) DESC

The table player includes the members of each team:

player

team	playername	pos
NOR	Andreas Rædergård Schjelderup	MID
CRO	Andrej Kramaric	FWD
PAN	Andres Alberto Andrade	DEF
SCO	Andrew Henry Robertson	DEF

Mexico City scorer positions

For every goal scored in Mexico City show the date played, the player and that player's position (pos)

SELECT played,player,pos
  FROM goal JOIN game ON goal.game=game.id
            JOIN player ON goal.player=player.playername
 WHERE city = 'Mexico City'

Defenders score

For each goal scored by a defender, show the player, their teamname.

In the default query, notice that the goal.team (the side credited with the goal) might not match the player's team (the person who scored the goal). This happens when there is an "own-goal".

SELECT player, goal.team as gteam,player.team as pteam
  FROM goal JOIN player ON goal.player=player.playername
 WHERE pos='DEF'

SELECT player, teamname
  FROM goal JOIN player ON goal.player=player.playername
            JOIN team ON player.team = team.id 
 WHERE pos='DEF'

Extra time goals

For each goal scored in extra time, show the player, their position, teamname and city

An extra time goal is when the gtime is BETWEEN 91 AND 120.

SELECT player, pos, teamname, city
  FROM goal JOIN player ON goal.player = player.playername
            JOIN game   ON goal.game = game.id
            JOIN team   ON player.team = team.id
 WHERE gtime BETWEEN 91 AND 120

What next?

JOIN Quiz

Old JOIN Tutorial

More JOIN operations: The next tutorial about the Movie database involves some slightly more complicated joins from the movie database.