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:
%pattern
), it matches any sequence of characters leading up to the specified pattern.pattern%
), it matches any sequence of characters that follow the specified pattern.st%ng
), it matches any sequence of characters between the specified patterns.Here's an example showing how the LIKE
operator is used:
SQL1-- 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:
SQL1-- 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:
SQL1-- 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!