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. It works with wildcard characters, such as the percentage % sign and the underscore _, to refine searches.

The percentage sign % matches zero, one, or multiple characters in a string pattern within the LIKE operator. See the usage examples below:

• A%: Matches any string starting with 'A' (e.g., 'Apple', 'Ant').
• %A: Matches any string ending with 'A' (e.g., 'Pizza', 'Alpaca').
• %A%: Matches any string containing 'A' (e.g., 'Table', 'Laptop').

The underscore _ matches exactly one character. See the usage examples below:

• A_: Matches any two-character string starting with 'A' (e.g., 'An', 'At').
• _A: Matches any two-character string ending with 'A' (e.g., 'Ba', 'Ca').

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

-- Use LIKE operator to find all orders in January
SELECT order_id, order_date
FROM Orders
WHERE order_date LIKE '2021-01%';

-- Sneak peek of the output:
-- | order_id | order_date |
-- |----------|------------|
-- |        1 | 2021-01-15 |
-- |        2 | 2021-01-20 |

In the example above, we search for all orders in the Orders table whose order_date starts with '2021-01', using the LIKE operator and the % wildcard. This will return all orders placed in January 2021.

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:

-- Find OrderItems with extended support
SELECT product_name, category_id
FROM Products
WHERE category_id BETWEEN 2 AND 4;

-- Sneak peek of the output:
-- | product_name       | category_id |
-- |--------------------|-------------|
-- | Math Worksheets    |           2 |
-- | Science Guides     |           3 |
-- | Language Podcasts  |           4 |

In this query, the BETWEEN operator is used to filter products from the Products table whose category_id falls between 2 and 4 (inclusive).