Mastering Subqueries for Data Analysis

Hello again! In our previous lessons, we explored the functionality of Logical AND/OR operations and delved into conditional operators in SQL such as LIKE, IN, and BETWEEN. We delved deeply into how these tools not only allow us to filter and retrieve data accurately but also provide more nuanced control over our queries. In this unit, we'll build on those skills and introduce a new SQL concept—subqueries—which will enable us to perform even more complex data analysis.

So, what is a subquery? A subquery, also known as an "inner query" or "nested query," is a query nested within another SQL query. It allows us to solve convoluted problems that require multiple steps, making our SQL statements even more powerful. Essentially, a subquery can retrieve data for the primary or outer SQL query to utilize. Like other SQL queries you're now familiar with, they begin with a SELECT statement and conclude with an appropriate clause such as WHERE or FROM.

For instance, let's consider a straightforward subquery that calculates the average minute of events in the MatchEvents table:

SELECT AVG(minute) FROM MatchEvents;

-- Output:
--  AVG(minute) 
-- -------------
--    51.828125 

This subquery, when used within a larger query, can help us compare each event's minute to the average and filter out events that meet certain conditions.

Subqueries can be nested within other subqueries or queries. Nesting is the act of placing one item inside another. In the case of subqueries, nesting results in an outer query, possibly containing one or more subqueries. These subqueries can, in turn, contain further subqueries, enabling SQL to solve exceptionally complex tasks.

While the concept might appear intimidating initially, don't worry! The beauty of nested subqueries is that they can always be broken down into smaller, easier-to-understand steps.

Let's examine a straightforward example before we proceed to break down the final task. Suppose we want to find matches where the minute of an event is greater than the average minute of all events. Here's how we could accomplish this with a subquery:

SELECT 
    m.match_id AS MatchID,
    me.minute AS Minute,
    me.event_type AS EventType
FROM Matches m
INNER JOIN MatchEvents me ON m.match_id = me.match_id
WHERE me.minute > (
    SELECT AVG(minute)
    FROM MatchEvents
);

-- Sneak peek of the output:
-- | MatchID | Minute | EventType              |
-- |---------|--------|------------------------|
-- |       1 | 90+1   | Left-footed shot       |
-- |       5 | 82     | Left-footed shot       |

In this example:

• We introduce aliases m for Matches and me for MatchEvents to simplify references within the query.
• First, we perform an INNER JOIN on the Matches and MatchEvents tables on the match_id field.
• Next, we filter using a WHERE clause that compares each event's minute to the average minute (SELECT AVG(minute) FROM MatchEvents) of all events. This is our subquery.
• This query retrieves matches where at least one event occurred later than the average minute of events.

You have now seen how to use subqueries to tackle complex SQL problems!