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:

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:

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:

SQL
Copy to clipboard
1SELECT 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:

SQL
Copy to clipboard
1SELECT 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:

SQL
Copy to clipboard
1SELECT 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:

• INNER JOIN returns rows with matching values in both tables.
• LEFT JOIN holds all data from the left table, irrespective of whether there's a match in the right.
• RIGHT JOIN behaves similarly to LEFT JOIN but favors the right table.

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!