Great work! You've grasped the essentials and have now explored INNER JOIN while analyzing Lionel Messi's match history. Now it's time to delve deeper into SQL JOINs using the data from Messi's matches and career stats. We are going to focus on LEFT JOIN
and RIGHT JOIN
in this course. Before we start working with the more detailed soccer data at our disposal, it's vital for us to clearly understand these joins through a simpler example.
Before we start, keep in mind that SQL JOINs allow us to combine data from two or more tables based on a related column. We've previously worked with INNER JOIN
, which selects rows that have matching values in both tables. In this lesson, we'll see how LEFT JOIN
and RIGHT JOIN
can help us manipulate our data further.
To help understand these JOIN types,consider two simple tables: Matches
and MatchEvents
.
Matches Table:
match_id | season_id | competition_id | matchday | date | venue | club_id | opponent_id | result |
---|---|---|---|---|---|---|---|---|
1 | 1 | 1 | 34 | 2005-01-05 | H | 1 | 2 | 2:0 |
2 | 2 | 2 | Group Stage | 2005-11-02 | H | 1 | 2 | 5:0 |
The Matches
table provides detailed information about each match, including the match_id, date, result, and other relevant attributes like season, competition, venue, and participating clubs. This is useful for tracking the specifics of when, where, and against whom each match took place.
MatchEvents Table:
event_id | match_id | playing_position | minute | at_score | event_type | goal_assist_id |
---|---|---|---|---|---|---|
1 | 1 | CF | 90+1 | 2:0 | Left-footed shot | 1 |
2 | 2 | RW | 34 | 3:0 | Left-footed shot | NULL |
The MatchEvents
table helps to connect each match to the events that happened in it using corresponding match_id
values.
INNER JOIN
returns rows when there's a match in both tables. If there's no match, those rows are not included in the output.
Example:
SQL1SELECT Matches.match_id, Matches.result, MatchEvents.event_id, MatchEvents.event_type
2FROM Matches
3INNER JOIN MatchEvents ON Matches.match_id = MatchEvents.match_id;
4
5-- Sneak peek of the output:
6-- | match_id | result | event_id | event_type |
7-- |----------|--------|----------|------------------------|
8-- | 1 | 2:0 | 1 | Left-footed shot |
9-- | 2 | 5:0 | 2 | Left-footed shot |
LEFT JOIN
includes all rows from the left table, along with any matches from the right table. If there's no match, the output displays NULL
for the right table's columns.
Example:
SQL1SELECT Matches.match_id, Matches.result, MatchEvents.event_id, MatchEvents.event_type
2FROM Matches
3LEFT JOIN MatchEvents ON Matches.match_id = MatchEvents.match_id;
4
5-- Sneak peek of the output:
6-- | match_id | result | event_id | event_type |
7-- |----------|--------|----------|------------------------|
8-- | 1 | 2:0 | 1 | Left-footed shot |
9-- | 2 | 5:0 | 2 | Left-footed shot |
RIGHT JOIN
ensures that every row from the right table is included in the output, with matched rows from the left table.
Example:
SQL1SELECT Matches.match_id, Matches.result, MatchEvents.event_id, MatchEvents.event_type
2FROM Matches
3RIGHT JOIN MatchEvents ON Matches.match_id = MatchEvents.match_id;
4
5-- Sneak peek of the output:
6-- | match_id | result | event_id | event_type |
7-- |----------|--------|----------|------------------------|
8-- | 1 | 2:0 | 1 | Left-footed shot |
9-- | 2 | 5:0 | 2 | Left-footed shot |
Note: With the provided sample tables, there currently seems to be no visible difference due to the limited number of rows and the fact that each match has a corresponding event. However, in general, there is a difference: RIGHT JOIN includes all rows from the right table (MatchEvents) and any corresponding rows from the left table (Matches). It's advisable to try this on tables with more rows and different cases where some rows in the right table do not have corresponding rows in the left table to see the full effect.
Having explored INNER JOIN
, LEFT JOIN
, and RIGHT JOIN
in detail:
With a solid understanding of each JOIN type, we are now ready to explore even more complex queries using the Messi match and event dataset in upcoming lessons. Prepare to delve into FULL JOIN
in our next lesson, as we continue unraveling the subtleties of SQL JOINs. Journey onward in your SQL exploration!