Complex Queries and Conditional Logic

Hi there! You've made substantial progress, and I'm impressed with all you've learned so far. You've mastered the use of SQL logical and conditional operators and elevated your data analysis with subqueries. In this unit, we're diving into complex queries and conditional logic — an advanced SQL topic that will allow for even more intricate data queries and analysis.

We will continue to work with our sports database, focusing on the Matches and MatchEvents tables. As a quick reminder, the Matches table contains details such as match IDs, dates, and results, while MatchEvents holds information about events during matches, like minute and event type.

Are you ready to get started? Let's jump right in!

In SQL, the IF function is one of the most useful tools we have for adding conditional logic to our queries. It operates according to a simple principle: if a specific condition is true, then do something; if it's false, then do something else.

Let's put this into practice by categorizing matches into 'Recent' and 'Earlier' periods using an SQL query:

SELECT
    match_id AS MatchID,
    date AS MatchDate,
    IF(YEAR(date) > 2015, 'Recent Match', 'Earlier Match') AS MatchPeriod
FROM
    Matches;
    
-- Sneak peek of the output:
-- | MatchID | MatchDate  | MatchPeriod   |
-- |---------|------------|---------------|
-- |       1 | 2005-05-01 | Earlier Match |
-- |       2 | 2005-11-02 | Earlier Match |

In the query above, the IF function checks whether each match's date is after 2015. If the condition is true, 'Recent Match' is returned; otherwise, 'Earlier Match' is returned. This approach allows us to categorize matches based on their dates in a very efficient and readable manner.

The CASE statement is an exceptionally versatile tool in SQL, allowing us to execute actions based on various conditions, offering a functionality somewhat similar to the IF statement but with enhanced flexibility. Unlike the IF function, which is binary in its conditionality (true or false), the CASE statement supports multiple conditions, providing a robust means to handle complex logical operations within our queries.

Let's explore how we can categorize matches by their event time using the CASE statement:

SELECT
    m.match_id AS MatchID,
    me.minute AS Minute,
    CASE
        WHEN me.minute < 30 THEN 'Early'
        WHEN me.minute BETWEEN 30 AND 60 THEN 'Mid'
        ELSE 'Late'
    END AS EventTimeCategory
FROM
    MatchEvents me
JOIN
    Matches m ON me.match_id = m.match_id;
    
-- Sneak peek of the output:
-- | MatchID | Minute | EventTimeCategory |
-- |---------|--------|-------------------|
-- |       1 | 90+1   | Late              |
-- |       2 | 34     | Mid               |

In this example, we're joining the Matches and MatchEvents tables through the match_id field. Within our SELECT statement, the CASE is applied to determine a match event's time category. It sequentially evaluates each condition specified by the WHEN clauses until one is found true. If none of the WHEN conditions match, the ELSE clause (considered a default) is returned. Note that the ELSE clause is optional; if it is omitted and no conditions are met, the CASE statement will return NULL. Here's a closer look at the workflow:

• The CASE statement starts its evaluation.
• If the minute is less than 30, 'Early' is returned.
• If the minute is between 30 and 60, 'Mid' is returned.
• If neither condition is met, the ELSE part catches all remaining possibilities, returning 'Late'.

The conclusion of the CASE statement is marked by the END keyword, signifying the end of the conditional checks. Following END, we use AS EventTimeCategory to label the outcome of our CASE statement, assigning it as a new column in our results. This naming convention is not just for clarity; it’s essential for referencing the produced column in other parts of our query or in applications that consume this query's output.