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 online shopping database, focusing on the Orders and Products tables. The Orders table contains details such as order IDs, dates, and status, while Products holds information about products, including price and category.

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 orders into 'Recent' and 'Earlier' periods using an SQL query:

SQL
Copy to clipboard
1SELECT
2    order_id AS OrderID,
3    order_date AS OrderDate,
4    IF(YEAR(order_date) > 2022, 'Recent Order', 'Earlier Order') AS OrderPeriod
5FROM
6    Orders;
7    
8-- Sneak peek of the output:
9-- | OrderID | OrderDate   | OrderPeriod    |
10-- |---------|-------------|----------------|
11-- |       1 | 2021-08-17  | Earlier Order  |
12-- |       2 | 2022-04-03  | Earlier Order  |

In the query above, the IF function checks whether each order's date is after 2022. If the condition is true, 'Recent Order' is returned; otherwise, 'Earlier Order' is returned. This approach allows us to categorize orders 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 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 products by their price using the CASE statement:

SQL
Copy to clipboard
1SELECT
2    p.product_id AS ProductID,
3    p.product_price AS ProductPrice,
4    CASE
5        WHEN p.product_price < 20 THEN 'Low'
6        WHEN p.product_price BETWEEN 20 AND 100 THEN 'Mid'
7        ELSE 'High'
8    END AS ProductPriceCategory
9FROM
10    Products p;
11    
12-- Sneak peek of the output:
13-- | ProductID | ProductPrice | ProductPriceCategory |
14-- |-----------|--------------|----------------------|
15-- |         1 |        9.34  | Low                  |
16-- |         2 |       12.84  | Low                  |

In this example, we're selecting the Products table fields. The CASE is applied to determine a product's price 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 product_price is less than 20, 'Low' is returned.
• If the product_price is between 20 and 100, 'Mid' is returned.
• If neither condition is met, the ELSE part catches all remaining possibilities, returning 'High'.

The conclusion of the CASE statement is marked by the END keyword, signifying the end of the conditional checks. Following END, we use AS ProductPriceCategory 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.