Webbo3 Data Analysis Bootcamp · SQL Module · Lesson 3

Filtering Data: Mastering AND, OR, NOT, BETWEEN, LIKE, and IN for Precise Query Results

A deep-dive lesson on advanced filtering techniques in the WHERE clause, including logical operators, range filtering, pattern matching with wildcards, and list-based selection.

In the last lesson you learned to retrieve data with SELECT and to apply basic conditions with WHERE. That was the foundation. This lesson is about precision. Real data is messy, inconsistent, and vast. A manager does not ask for all customers. They ask for customers in Lagos and Abuja who registered in the last quarter but have not made a purchase yet. A logistics team does not need every order. They need orders placed between the 15th and 20th of the month, with tracking numbers that start with a specific prefix, excluding cancelled orders. Answering these questions requires combining multiple conditions, matching partial text, filtering ranges, and testing membership in lists. This lesson teaches you to write queries that return exactly the rows you need and nothing more. Precision in filtering is what separates a working query from a professional one.

1. AND, OR, and NOT in the WHERE Clause

The WHERE clause becomes powerful when you combine multiple conditions. The logical operators AND, OR, and NOT are the glue that lets you build complex filters from simple comparisons. Understanding how they interact is essential, because one misplaced OR can return ten thousand rows you did not want.

AND: every condition must be true. AND is restrictive. It narrows your result set by requiring that every condition connected by it evaluates to true. If you want customers who are both active and located in Lagos, you use AND:

SELECT first_name, last_name, email, region
FROM customers
WHERE is_active = TRUE
  AND region = 'Lagos';

This returns only rows where both conditions are satisfied. A customer in Lagos who is inactive is excluded. An active customer in Abuja is excluded. Only the intersection survives. You can chain as many AND conditions as necessary:

SELECT * FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <= '2026-03-31'
  AND status = 'delivered'
  AND total_amount > 50000;

This returns orders from the first quarter of 2026 that were delivered and had a total value above fifty thousand naira. Every condition must pass. If one fails, the row is discarded.

OR: at least one condition must be true. OR is expansive. It broadens your result set by accepting rows that satisfy any one of the connected conditions. If you want customers from either Lagos or Abuja, you use OR:

SELECT first_name, last_name, region
FROM customers
WHERE region = 'Lagos'
  OR region = 'Abuja';

This returns customers from Lagos, customers from Abuja, and customers who happen to be in both if your data allows duplicates. OR is inclusive, not exclusive. A common mistake is to assume OR behaves like exclusive or, meaning one or the other but not both. In SQL, if both conditions are true, the row is still included.

The danger of mixing AND and OR without parentheses. This is the most common source of logic errors in SQL queries. MySQL evaluates AND before OR by default, which often produces results you did not intend. Consider this query:

SELECT * FROM customers
WHERE region = 'Lagos'
  OR region = 'Abuja'
  AND is_active = TRUE;

Without parentheses, MySQL interprets this as: region equals Lagos, OR, region equals Abuja and is_active is true. This means customers in Lagos are returned regardless of whether they are active, because the AND binds more tightly than the OR. If you intended to find only active customers in either Lagos or Abuja, you must use parentheses:

SELECT * FROM customers
WHERE (region = 'Lagos' OR region = 'Abuja')
  AND is_active = TRUE;

Now the logic is explicit: first evaluate the OR inside the parentheses to get all customers in Lagos or Abuja, then apply the AND to keep only those who are active. Whenever a query contains both AND and OR, use parentheses. There is no performance penalty, and the clarity is worth more than the few extra keystrokes.

NOT: reversing a condition. NOT negates whatever follows it. It is useful when you want everything except a specific subset:

SELECT * FROM customers WHERE NOT region = 'Lagos';

This returns all customers who are not in Lagos. NOT can also be combined with AND and OR, though again, parentheses are your friend:

SELECT * FROM customers
WHERE NOT (region = 'Lagos' AND is_active = TRUE);

This returns everyone except active customers in Lagos. It includes inactive Lagos customers, active Abuja customers, and inactive Abuja customers. The parentheses ensure the NOT applies to the entire combined condition, not just the first part.

2. BETWEEN: Filtering a Range

BETWEEN is a concise way to test whether a value falls inside a range. It is inclusive, meaning both endpoints are included in the result. It works for numbers, dates, and even text, though it is most commonly used for numeric and date ranges.

Numeric ranges. To find orders with a total amount between twenty thousand and fifty thousand naira, inclusive:

SELECT * FROM orders
WHERE total_amount BETWEEN 20000 AND 50000;

This is exactly equivalent to total_amount >= 20000 AND total_amount <= 50000. BETWEEN is shorter to write and often more readable, especially when the range boundaries are long expressions. The lower bound must come first. If you write BETWEEN 50000 AND 20000, MySQL evaluates it literally and returns no rows because the lower bound is greater than the upper bound.

Date ranges. BETWEEN is particularly clean for date filtering:

SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

This returns every order from January 1 through March 31, 2026. Because BETWEEN is inclusive, an order placed exactly on March 31 at 11:59 PM is included. However, if your column is a DATETIME rather than a DATE, you must be careful. The expression '2026-03-31' is implicitly treated as '2026-03-31 00:00:00'. So an order placed at 2:00 PM on March 31 is actually greater than '2026-03-31 00:00:00' and would be excluded. To capture the full last day when using DATETIME, write the upper bound as the next day at midnight and use an exclusive comparison, or use the DATE function to strip the time component:

SELECT * FROM orders
WHERE DATE(order_date) BETWEEN '2026-01-01' AND '2026-03-31';

The DATE function converts a DATETIME to a DATE, stripping the time portion, so the comparison works as expected. Be aware that applying a function to a column like this can prevent MySQL from using an index on that column, which may slow down queries on very large tables. For small to medium tables, the clarity is worth the trade-off.

Negating with NOT BETWEEN. To find everything outside a range, prefix BETWEEN with NOT:

SELECT * FROM orders
WHERE total_amount NOT BETWEEN 20000 AND 50000;

This returns orders below twenty thousand and orders above fifty thousand. It is equivalent to total_amount < 20000 OR total_amount > 50000. As with all NOT operations, be mindful of NULL values. If total_amount is NULL for some rows, those rows are excluded from both BETWEEN and NOT BETWEEN results, because NULL cannot be compared.

3. LIKE: Pattern Matching

Exact equality is not enough when you are dealing with text that varies. A customer might type their email as @gmail.com or @GMAIL.COM. A product code might have a consistent prefix but varying suffixes. The LIKE operator lets you match text against a pattern rather than a fixed value.

The percent wildcard: %. The percent sign represents any sequence of zero or more characters. It is the most flexible wildcard:

SELECT * FROM customers WHERE first_name LIKE 'A%';

This returns customers whose first name starts with the letter A, including Ada, Adewale, and Augustine. The % after A means any characters can follow, or none at all. To find names that end with a specific suffix, place the % at the beginning:

SELECT * FROM customers WHERE email LIKE '%@gmail.com';

This returns every customer whose email address ends with @gmail.com, regardless of what comes before it. To find text that contains a substring anywhere in the column, use % on both sides:

SELECT * FROM products WHERE product_name LIKE '%phone%';

This returns products with names containing phone anywhere: iPhone, Samsung Phone Case, Phone Charger, and Wireless Headphone. If you need the match to be case-insensitive, which is usually the case for names and emails, MySQL's default collation is case-insensitive for LIKE in most installations. If your installation uses a case-sensitive collation, you can force case-insensitivity with the LOWER function:

SELECT * FROM products WHERE LOWER(product_name) LIKE '%phone%';

The underscore wildcard: _. The underscore represents exactly one single character. It is more precise than %:

SELECT * FROM customers WHERE last_name LIKE 'Okafo_';

This returns last names that are exactly six characters long, starting with Okafo and ending with any single character: Okafor, Okafoe, Okafoa. It does not return Okafora, because that is seven characters, nor does it return Okafo, because that is five characters and the underscore demands exactly one more. You can use multiple underscores:

SELECT * FROM products WHERE product_code LIKE 'INV__2026';

This matches product codes that start with INV, followed by exactly two characters, followed by 2026. INVAB2026 matches. INV1232026 does not, because 123 is three characters. Combining % and _ in one pattern gives you fine-grained control:

SELECT * FROM customers WHERE phone LIKE '0803-___-____';

This matches phone numbers that start with 0803-, followed by exactly three digits, a hyphen, and exactly four digits. It is a crude but effective way to validate format patterns in a query.

Escaping wildcards when searching for literal % or _. If you actually need to search for a literal percent sign or underscore in your data, you must escape it using the backslash or a custom escape character:

SELECT * FROM products WHERE description LIKE '%50\% off%';

The backslash tells MySQL to treat the following % as a literal character, not a wildcard. This returns products with descriptions containing 50% off. Without the backslash, the % would match any characters and the query would behave unpredictably.

4. IN and NOT IN: Matching a List of Values

When you need to test whether a column matches any value from a specific list, IN is cleaner and more efficient than chaining multiple OR conditions. It is also easier to maintain, because adding a new value to the list requires editing one place instead of adding another OR clause.

Basic IN syntax. To find customers in Lagos, Abuja, or Port Harcourt:

SELECT first_name, last_name, region
FROM customers
WHERE region IN ('Lagos', 'Abuja', 'Port Harcourt');

This is equivalent to region = 'Lagos' OR region = 'Abuja' OR region = 'Port Harcourt', but it is shorter, more readable, and less prone to typos. The list inside the parentheses can contain any number of values, separated by commas. The values can be numbers, text, or dates, as long as they match the column's data type.

IN with numeric values. IN works equally well for numbers:

SELECT * FROM products WHERE category_id IN (1, 3, 5, 7);

This returns products in categories 1, 3, 5, or 7. Numeric values in an IN list do not need quotes. Text values do. Mixing them up is a common syntax error.

IN with subqueries. One of the most powerful uses of IN is with a subquery, where the list of values is generated dynamically by another SELECT statement:

SELECT * FROM orders
WHERE customer_id IN (
  SELECT customer_id FROM customers WHERE region = 'Lagos'
);

This returns all orders placed by customers who are in Lagos. The inner query generates a list of customer IDs, and the outer query uses IN to filter orders against that list. You will learn more about subqueries in a later lesson, but this pattern is worth recognizing now because it appears constantly in real-world reporting.

NOT IN: excluding a list. NOT IN returns rows where the column value does not match any item in the list:

SELECT * FROM customers
WHERE region NOT IN ('Lagos', 'Abuja');

This returns customers in every region except Lagos and Abuja. However, NOT IN has a critical behavior you must understand. If the list contains even one NULL value, NOT IN returns no rows at all. This is because SQL uses three-valued logic: true, false, and unknown. When comparing a value against NULL, the result is unknown. NOT IN requires the comparison to be definitively false for every item in the list. If any comparison is unknown because of NULL, the overall result becomes unknown, and unknown rows are excluded from the output. If your list might contain NULLs, use a LEFT JOIN with an IS NULL check instead, or ensure your subquery filters out NULLs.

IN versus BETWEEN for ranges. Use IN when you have a discrete list of specific values. Use BETWEEN when you have a continuous range. Do not use IN for ranges that could be expressed with BETWEEN, because IN lists become unwieldy. Writing IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) is verbose and harder to maintain than BETWEEN 1 AND 10. Conversely, do not use BETWEEN for disconnected values. BETWEEN 1 AND 10 AND BETWEEN 20 AND 30 is less clear than IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30), though in practice you would use two BETWEEN conditions joined by OR for that case.

Using AI to Move Faster in SQL Filtering

The operators in this lesson are not difficult individually, but combining them into correct, efficient queries for real business questions takes practice. AI can accelerate that practice by generating starting queries, explaining logic errors, and helping you translate ambiguous requirements into precise SQL.

1. Translate complex business questions into SQL with natural language.
When a stakeholder asks for something convoluted, for example "Show me all orders from the last quarter in Lagos or Abuja that were above fifty thousand naira, placed by active customers, except for orders that were cancelled or refunded," you can paste that sentence into Copilot and ask: "Convert this business requirement into a MySQL WHERE clause using AND, OR, NOT, and IN." AI will likely produce something like WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31' AND region IN ('Lagos', 'Abuja') AND total_amount > 50000 AND is_active = TRUE AND status NOT IN ('cancelled', 'refunded'). Your job is to verify the date range matches your actual quarter boundaries, confirm the region and status values match your database exactly, and add parentheses where needed for clarity.

2. Use AI to debug logic errors in complex WHERE clauses.
If your query returns too many rows or too few, paste the query and your intended logic into an AI assistant: "This query is supposed to return only active customers in Lagos or Abuja, but it is also returning inactive customers in Lagos. What is wrong?" AI will identify that your AND and OR are not grouped with parentheses, or that a NOT is applying to the wrong condition. This is faster than manually tracing three-valued logic, especially when NULL values are involved.

3. Generate test data that exercises every edge case.
To truly master BETWEEN, LIKE, and IN, you need data with boundary values, partial matches, NULLs, and case variations. Ask AI: "Generate twenty INSERT statements for a customers table that will test these edge cases: names starting with A and ending with z, emails from gmail and yahoo, phone numbers with various prefixes, some NULL regions, and registration dates exactly on quarter boundaries." Insert this data, then write queries against it and verify the results manually. This deliberate practice is how you build confidence that your filters behave correctly in production.

4. Refactor verbose OR chains into IN or BETWEEN.
If you inherit a query with a long chain of OR conditions, like status = 'pending' OR status = 'processing' OR status = 'shipped' OR status = 'delivered', ask AI: "Refactor this WHERE clause to use IN, and explain whether it improves readability or performance." AI will rewrite it as status IN ('pending', 'processing', 'shipped', 'delivered') and explain that while the performance difference is usually negligible on small tables, the maintenance benefit is significant because adding or removing a status requires editing only one list instead of multiple OR clauses.

5. Verify AI-generated queries against your schema before executing.
AI does not know your column names, your data types, or whether your region column stores 'Lagos' or 'LG'. It might write BETWEEN '2026-01-01' AND '2026-03-31' when your dates are stored as DATETIME and need time-component handling. It might use LIKE '%phone%' on a column that is actually named product_title. Always compare the generated SQL to your actual schema using DESCRIBE table_name. Adjust column names, data types, and literal values to match reality. Treat AI output as a first draft from a junior developer who understands SQL syntax but not your specific database.

A habit worth building from this lesson onward: before writing a complex WHERE clause, state the business logic in plain English first, then ask AI to convert it to SQL, then audit the result for correct operator precedence, proper parentheses, and accurate column references. This workflow prevents the most common SQL errors: logic that looks right but evaluates wrong because of precedence rules you forgot, or because a column name changed since the AI was trained.

Next lesson: sorting results with ORDER BY, eliminating duplicates with DISTINCT, and limiting results with LIMIT.