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:

SQL
Copy to clipboard
1-- Use LIKE operator to find all matches played in 2005
2SELECT match_id, date
3FROM Matches
4WHERE date LIKE '2005%';
5
6-- Output:
7-- | match_id | date       |
8-- |----------|------------|
9-- |        1 | 2005-05-01 |
10-- |        2 | 2005-11-02 |
11-- |        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:

SQL
Copy to clipboard
1-- Use BETWEEN operator to choose events that occurred in the first half
2SELECT event_id, minute
3FROM MatchEvents
4WHERE minute BETWEEN 1 AND 45;
5
6-- Sneak peek of the output:
7-- | event_id | minute |
8-- |----------+--------+
9-- |        2 | 34     |
10-- |        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).

The IN operator in SQL allows us to filter data based on multiple values in a WHERE clause. It serves as a shorthand for multiple OR conditions and it's often used when there's a need to compare a column with more than two values.

Consider this example of a SQL query using the IN operator:

SQL
Copy to clipboard
1-- Find matches from specific competitions and list their results
2SELECT 
3    m.match_id AS MatchID,
4    m.result AS Result,
5    me.event_type AS EventType
6FROM Matches m
7JOIN MatchEvents me ON m.match_id = me.match_id
8WHERE m.competition_id IN (1, 2) AND me.event_type = 'Left-footed shot';
9
10-- Sneak peek of the output:
11-- | MatchID | Result | EventType        |
12-- |---------|--------|------------------|
13-- |       1 | 2:0    | Left-footed shot |
14-- |       2 | 5:0    | Left-footed shot |

In this example, the IN operator permits us to select matches whose competition_id is either 1 or 2, and the event_type in the MatchEvents table is 'Left-footed shot'. The table aliases m and me are used to make the query concise — m represents the Matches table and me represents the MatchEvents table. This helps in writing shorter and more readable SQL queries.

Great work so far, and congratulations! By reaching this point, you've developed a solid understanding of SQL conditional operators. Remember, these are powerful tools — when used correctly, they can make your data querying more detailed and precise.

Next up, you're going to get some hands-on practice. Applying the new skills you've acquired in different scenarios will reinforce your understanding and enhance your SQL proficiency. Look forward to applying these conditional operators in multiple contexts. You're doing great so far, and I'm confident that you'll effectively tackle the next section. Let's move on to the practice component and keep the SQL learning momentum going strong!