Salute and welcome back! It's splendid to see you eager to sink deeper into the cosmos of SQL JOINs through our athletic sentinel, Leo Messi's career statistics. Previously, we cast light on distinct kinds of JOINs and toured separately through the rich tables in our football database. Our next itinerary involves an in-depth discussion of INNER JOIN
.
In this session, we aim to detail one vital type of JOIN: the INNER JOIN. Essentially, the INNER JOIN
in SQL is a clause that merges rows from two tables based on a shared column between them. The output is a result set that exclusively polishes the rows satisfying the match condition.
Before we rush headlong into any particular examples, let's take a minute to inspect the standard syntax for running an INNER JOIN
:
SQL1SELECT column1, column2, ...
2FROM table1
3INNER JOIN table2
4ON table1.column_name = table2.column_name;
This syntax is the scaffolding of our queries anytime we desire to intertwine data from two interconnected tables. We'll explore the practical interpretation of this structure in joining Matches
and MatchEvents
later in this lesson.
By the completion of this lesson, you should be skilled at fetching match and event data from the MatchEvents
and Matches
tables using INNER JOIN
. The key match_id
will serve as the common link between these two tables.
Let's inaugurate with a barebones example:
Suppose we have a MatchEvents
table showcasing the event Left-footed shot
. This event can be found in the Matches
table under the match_id
12345
. We can cleverly exploit an INNER JOIN
to unify these two tables:
SQL1SELECT Matches.date, MatchEvents.event_type
2FROM Matches
3INNER JOIN MatchEvents
4ON Matches.match_id = MatchEvents.match_id;
5
6-- Sneak peek of the output:
7-- date | event_type |
8-- |------------|---------------------|
9-- | 2005-05-01 | Left-footed shot |
10-- | 2005-11-02 | Left-footed shot |
In this SQL statement:
SELECT Matches.date, MatchEvents.event_type
designates the information we desire — date
from the Matches
table and event_type
from the MatchEvents
table.FROM Matches
marks out the table we launch our query from.INNER JOIN MatchEvents
specifies our aim to associate the Matches
table with the MatchEvents
table.ON Matches.match_id = MatchEvents.match_id
is the crucial condition hooking up these tables. It states that the join should manifest on the match_id
columns, which should exist in both tables. This condition certifies that rows from Matches
and MatchEvents
are matched correctly.After executing the INNER JOIN
query on the Matches
and MatchEvents
tables, the result engenders an exhaustive list that matches each event with its respective date. This output exemplifies the power of INNER JOIN
to proficiently bind related data from two tables, delivering a crystal-clear overview of the association between matches and events.
We polish this topic by fetching all the event types and their corresponding match dates in descending order of match_id
. Here is the template for the query.
SQL1SELECT Matches.date, MatchEvents.event_type
2FROM Matches
3INNER JOIN MatchEvents
4ON Matches.match_id = MatchEvents.match_id
5ORDER BY Matches.match_id DESC;
6
7-- Sneak peek of the output:
8-- | date | event_type |
9-- |------------|------------------------|
10-- | 2023-03-04 | Left-footed shot |
11-- | 2023-02-26 | Right-footed shot |
In this scenario, the ORDER BY Matches.match_id DESC
condition organizes the results in descending order by match ID.
Well done! You've conquered much territory over this lesson. You've mastered the INNER JOIN instruction to pull out specific data from two interlaced tables. This SQL instrument is priceless in exploring the intricate connections buried within databases. Continue practicing, and before you know it, you'll be churning out SQL statements just like Messi scores goals!