Utilizing Conditional Operators - LIKE, IN, and BETWEEN

In our previous lesson, we tackled the foundational knowledge of the logical operators AND/OR in SQL. Now, we're going to extend this knowledge by introducing SQL conditional operators, which will further enhance the precision and detail of your queries. These operators include LIKE, BETWEEN, and IN.

In essence, conditional operators in SQL allow us to filter the output of our SQL queries based on certain criteria or conditions. They're used in conjunction with the SQL WHERE clause to specify the conditions that data must meet to be included in the query results. Let's delve into each of these conditional operators.

The LIKE operator in SQL is used in a WHERE clause to search for a specified pattern within a column. More often than not, it works with wildcard characters, such as the percentage % sign, which can represent zero, one, or multiple characters.

The % sign is versatile:

• When used at the beginning of a string (e.g., %pattern), it matches any sequence of characters leading up to the specified pattern.
• When used at the end of a string (e.g., pattern%), it matches any sequence of characters that follow the specified pattern.
• When used in the middle of a string (e.g., st%ng), it matches any sequence of characters between the specified patterns.

Here's an example showing how the LIKE operator is used:

-- Use LIKE operator to find all matches played in 2005
SELECT match_id, date
FROM Matches
WHERE date LIKE '2005%';

-- Output:
-- | match_id | date       |
-- |----------|------------|
-- |        1 | 2005-05-01 |
-- |        2 | 2005-11-02 |
-- |        3 | 2005-11-27 |

In the above example, we search for all matches in the Matches table whose date starts with '2005', using the LIKE operator and the % wildcard. This will return all matches played in the year 2005.

The BETWEEN operator in SQL is used to select values within a specific range. These values can be numbers, text, or dates. It is used with the WHERE clause.

The syntax for using BETWEEN is column_name BETWEEN value1 AND value2, where value1 and value2 define the range within which to search. It is important to note that BETWEEN is inclusive of both value1 and value2.

Here's an example of a SQL query that uses the BETWEEN operator:

-- Use BETWEEN operator to choose events that occurred in the first half
SELECT event_id, minute
FROM MatchEvents
WHERE minute BETWEEN 1 AND 45;

-- Sneak peek of the output:
-- | event_id | minute |
-- |----------+--------+
-- |        2 | 34     |
-- |        8 | 42     |

In this query, the BETWEEN operator is used to filter events from the MatchEvents table whose minute falls between 1 and 45 (inclusive).