In this lesson, you will learn how to use logical predicates to narrow down your result sets by creating specific conditions for the data you want to retrieve.
Moving Beyond Simple Selection
So far, we have discussed how to retrieve data from a table. However, we rarely want every single row. Usually, we only want rows that satisfy certain criteria—like customers who live in a specific city or products that cost more than a certain amount. We achieve this by adding a WHERE clause to our SELECT statement, which acts as a filter for our data.
Comparison Operators
At the heart of filtering are comparison operators. These allow you to compare a column’s value against a specific benchmark. Common operators include:
=(equal to)<>or!=(not equal to)>(greater than)<(less than)
For example, if you have a Products table, you could fetch only the expensive items:
SELECT product_name
FROM Products
WHERE price > 100;
Combining Logic with Boolean Operators
Often, one condition isn't enough. We can combine multiple conditions using Boolean logic operators: AND, OR, and NOT.
AND: Requires both conditions to be true. Use this to make your filter more restrictive.OR: Requires at least one condition to be true. Use this to broaden your search.NOT: Reverses the logic, selecting rows that do not match the condition.
Imagine we want to find luxury items that are also currently in stock:
SELECT product_name
FROM Products
WHERE price > 100 AND stock_quantity > 0;
When you combine these, the database engine evaluates each row against these predicates. If a row satisfies the logic, it is included in the output; if not, it is filtered out. Remember our work with Three-Valued Logic: if a comparison involves a NULL value, the result is "Unknown," which behaves like "False" in a filter—the row will be excluded from your results.
Key Takeaway: Logical predicates act as the gatekeepers of your queries, using comparison and Boolean operators to determine exactly which rows meet your criteria.