Advanced Filtering Operators: SQL Ranges, AND/OR Logic

Previously in this course, we explored Filtering Data with WHERE: A Guide to SQL Operators, where we learned to retrieve specific rows using basic equality. In this lesson, we level up your query capabilities by adding comparison operators, logical operators, and range filtering to your SQL toolkit.

As we continue building our store database, you'll often need to find products that aren't just "equal to" a value, but "more expensive than" or "within a specific price window."

Comparison Operators: Moving Beyond Equality

While equality (=) is useful, real-world data often requires relative comparisons. PostgreSQL provides a standard set of comparison operators that allow you to slice data based on numerical or temporal values.

Worked Example: Finding Expensive Products

Suppose you want to find all items in your products table that cost more than $50.00. You can use the > operator to filter these records immediately:

SQL
SELECT product_name, price
FROM products
WHERE price > 50.00;

This query returns every row where the value in the price column is strictly greater than 50.

Logical Operators: Combining Conditions

Often, a single filter isn't enough. You might need to find products that are both "expensive" AND "in stock," or items that belong to one category OR another.

• AND: Returns true only if all conditions are met.
• OR: Returns true if at least one condition is met.
• NOT: Inverts the result of a condition.

Worked Example: Combining Logic

If you want to find products that cost more than $50.00 and have more than 10 units in stock, you combine your filters:

SQL
SELECT product_name, price, stock_quantity
FROM products
WHERE price > 50.00
  AND stock_quantity > 10;

Filtering Data Using Ranges

For tasks like finding products within a price range, you could use price >= 10 AND price <= 20. However, SQL provides a cleaner syntax specifically for inclusive ranges: the BETWEEN operator.

Worked Example: Using BETWEEN

The BETWEEN operator is inclusive, meaning it includes the start and end values.

SQL
SELECT product_name, price
FROM products
WHERE price BETWEEN 10.00 AND 20.00;

This is functionally equivalent to price >= 10.00 AND price <= 20.00, but it is much easier to read and maintain as your queries grow in complexity.

Hands-On Exercise

Using your store database, try the following tasks to practice your filtering skills:

1. Write a query to find all products with a price of $0.00 (perhaps they are free samples or out-of-stock placeholders).
2. Find all products that are either priced under $5.00 or priced over $100.00.
3. Retrieve all products that are priced between $20.00 and $50.00 and have a stock_quantity of 0.

Common Pitfalls

• Operator Precedence: When mixing AND and OR, the AND operator is evaluated first. If you need an OR condition to be evaluated before an AND, always use parentheses: WHERE (category = 'Electronics' OR category = 'Office') AND price < 50.
• NULL values: Comparing NULL with standard operators (like price > 0) will not return the row if the value is NULL. NULL represents "unknown," so it is neither greater than nor less than any number. We will cover how to handle these specifically in future lessons.
• BETWEEN Confusion: Remember that BETWEEN is inclusive. If you need an exclusive range (e.g., strictly greater than 10 and strictly less than 20), stick to the standard < and > operators.

FAQ

Q: Can I use comparison operators on dates? A: Yes! PostgreSQL handles dates intuitively. created_at > '2023-01-01' will correctly filter for records created after the start of 2023.

Q: Is != or <> better? A: Both work in PostgreSQL. <> is the SQL standard, while != is common in other programming languages. Use whichever you prefer, but be consistent.

Q: Does BETWEEN work on text? A: It does, as it performs an alphabetical comparison. However, this is rarely useful for product names and can lead to confusing results. Stick to using it for numbers and dates.

Recap

You now know how to refine your data retrieval beyond simple equality. By using comparison operators (>, <), logical operators (AND, OR), and the BETWEEN keyword, you can build highly specific queries that surface exactly the data you need from your store database.

Up next: Sorting Query Results — we will learn how to organize your filtered data to make it more readable and useful.