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:

SQL
Copy to clipboard
1SELECT
2    match_id AS MatchID,
3    date AS MatchDate,
4    IF(YEAR(date) > 2015, 'Recent Match', 'Earlier Match') AS MatchPeriod
5FROM
6    Matches;
7    
8-- Sneak peek of the output:
9-- | MatchID | MatchDate  | MatchPeriod   |
10-- |---------|------------|---------------|
11-- |       1 | 2005-05-01 | Earlier Match |
12-- |       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:

SQL
Copy to clipboard
1SELECT
2    m.match_id AS MatchID,
3    me.minute AS Minute,
4    CASE
5        WHEN me.minute < 30 THEN 'Early'
6        WHEN me.minute BETWEEN 30 AND 60 THEN 'Mid'
7        ELSE 'Late'
8    END AS EventTimeCategory
9FROM
10    MatchEvents me
11JOIN
12    Matches m ON me.match_id = m.match_id;
13    
14-- Sneak peek of the output:
15-- | MatchID | Minute | EventTimeCategory |
16-- |---------|--------|-------------------|
17-- |       1 | 90+1   | Late              |
18-- |       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.

Great job! You've just learned to use the IF function and CASE statement to write more advanced SQL queries. You now have the skills to filter data more accurately, write conditional statements in SQL, and analyze real-world data more effectively.

Next, try your hands at a series of practice exercises on CodeSignal. We have prepared these exercises to reinforce what you have learned in this lesson. The more you practice these skills, the more confident you'll become in handling real-world scenarios.

Congratulations! You are almost at the end of this course. You should be incredibly proud of your progress. These tools are powerful additions to your data analysis arsenal, and you'll find them invaluable in your future work. Keep practicing and keep learning. We're excited to see where your new skills will take you!