Filtering Data with IN, BETWEEN, and LIKE

The IN Operator: Checking for Membership in a Set

In the last lesson, you saw how to find rows matching one of several values using OR:

SELECT * FROM customers WHERE city = 'London' OR city = 'Paris' OR city = 'Tokyo';

This works, but it quickly becomes repetitive and hard to read as the list grows. The IN operator provides a much more elegant solution by testing if a value is present within a specified list (or set) of values.

The syntax is:
WHERE column_name IN (value1, value2, ...)

Let's watch a short video that demonstrates how IN cleans up a query that would otherwise need multiple OR statements.

As you saw, the IN operator simplifies the query significantly. Our previous example becomes:

SELECT * FROM customers WHERE city IN ('London', 'Paris', 'Tokyo');

This is not only easier to write but also much clearer to read, which is vital for maintaining code. You can also use the NOT IN operator to find all rows where the value does not match any item in the list.

SELECT *
FROM bugs
WHERE status IN ('NEW', 'ASSIGNED', 'REOPENED');

The BETWEEN Operator: Filtering Within a Range

Similarly, filtering for a range of values can be done with AND:

SELECT * FROM orders WHERE order_date >= '2023-06-01' AND order_date <= '2023-06-30';

The BETWEEN operator provides a more concise and readable way to express the same logic. It checks if a value falls within a specified range.

The syntax is:
WHERE column_name BETWEEN lower_bound AND upper_bound

A crucial detail is that the BETWEEN operator is inclusive: it includes both the lower and upper bounds in the range. So BETWEEN 10 AND 20 is equivalent to >= 10 AND <= 20.

The following video demonstrates BETWEEN with numbers, text, and its inverse, NOT BETWEEN.

This image provides a great visual summary of how BETWEEN works.

For a more in-depth textual explanation, you can read the following sections.

SELECT *
FROM logs
WHERE response_time_ms BETWEEN 500 AND 1000;

The LIKE Operator: Flexible Pattern Matching

The IN and BETWEEN operators are powerful shortcuts for logic you could already write with AND and OR. The LIKE operator, however, introduces a completely new capability: pattern matching for strings. This is incredibly useful in technical support and development, where you might need to search for error messages, log entries, or user data without knowing the exact text.

LIKE is used with two special wildcard characters:

• % (Percent sign): Matches any sequence of zero or more characters.
• _ (Underscore): Matches exactly one single character.

Let's see some common patterns:

This visual shows a simple LIKE query in action.

The following article offers a comprehensive guide to using LIKE with many practical examples. Given your interest in logical puzzles, you might find crafting these patterns quite engaging.

A Note on Case Sensitivity

As the article mentions, LIKE is case-sensitive in many database systems (like PostgreSQL and Oracle) but case-insensitive by default in others (like MySQL and SQL Server). To write portable, predictable code, it is best practice to force a case conversion using LOWER() or UPPER() on both the column and the pattern string.

Case-sensitive (might miss results):
WHERE last_name LIKE 'smith%' -- Won't find 'Smith'

Case-insensitive (robust):
WHERE LOWER(last_name) LIKE 'smith%' -- Finds 'smith', 'Smith', 'SMITH', etc.

SELECT *
FROM users
WHERE username LIKE '____n';

Conclusion

Great work! You've just added three powerful tools to your SQL toolkit that go beyond simple equality checks. These operators will make your queries more concise, readable, and capable.

Key Takeaways:

• IN: Use this to check if a value exists within a list of discrete options. It's a cleaner alternative to multiple OR conditions.
• BETWEEN: Use this to check if a value falls within an inclusive range. It's more readable than using AND with >= and <=.
• LIKE: Use this for powerful string pattern matching. The % wildcard matches any number of characters, while _ matches just one. Remember to use LOWER() for reliable case-insensitive searches.

Preview of the Next Lesson:
So far, we've assumed that every column in every row has a value. But in the real world, data is often incomplete. How do we find rows where a value is missing? In the next lesson, we will cover how to handle NULL values in your queries using the IS NULL and IS NOT NULL operators. This is a fundamental concept for cleaning data and handling exceptions—a daily reality in both technical support and software development.