Combining Conditions with AND and OR

Logical Operators: AND and OR

When filtering data, we often need to check if multiple conditions are met simultaneously or if any one of several conditions is met. This is where the AND and OR operators come into play.

• AND: Use this when you need rows that satisfy all of the specified conditions.
• OR: Use this when you need rows that satisfy at least one of the specified conditions.

The following image shows the truth tables for these operators, which provide a clear, logical map of their behavior. In these tables, 1 represents TRUE and 0 represents FALSE.

Let's break them down one by one.

The AND Operator: Combining "Must-Haves"

The AND operator is used to filter results based on two or more conditions that must all be true. Think of it as a series of gates; a row of data must pass through every gate to be included in the final result.

The syntax is straightforward:

SELECT column_name(s)
FROM table_name
WHERE condition1 AND condition2 AND ...;

Let's watch a quick demonstration.

For example, if you wanted to find all employees in the 'Sales' department who were hired after January 1, 2023, you would write:

SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales' AND hire_date > '2023-01-01';

Only employees who meet both criteria will be returned.

SELECT *
FROM products
WHERE category = 'Electronics' AND stock_quantity < 10;

The OR Operator: Providing Alternatives

The OR operator is used when you want to retrieve rows that meet at least one of several conditions. It's useful when you're looking for data that could fall into one of a few different buckets.

The syntax is similar to AND:

SELECT column_name(s)
FROM table_name
WHERE condition1 OR condition2 OR ...;

Here is another quick clip showing the OR operator in action.

For instance, to find all customers who live in either 'New York' or 'London', you would write:

SELECT customer_name, city
FROM customers
WHERE city = 'New York' OR city = 'London';

This query will return customers from both cities.

Combining AND and OR: The Importance of Precedence

This is where things get interesting and where a precise, logical approach is critical. What happens when you mix AND and OR in the same WHERE clause?

SQL evaluates AND operators before OR operators. This is a fundamental rule of operator precedence, similar to how multiplication is done before addition in mathematics.

Let's say you want to find all products that are in Category 1 or Category 2, and you only want to see products with a price greater than $15. A common mistake would be to write:

-- This query is logically flawed!
SELECT *
FROM products
WHERE category_id = 1 OR category_id = 2 AND unit_price > 15;

Because AND is evaluated first, the database interprets this as:
"Find all products where category_id = 1... OR... find all products where (category_id = 2 AND unit_price > 15)."

This is not what we wanted! We would get all products from Category 1, regardless of price.

The following video perfectly illustrates this common pitfall and how to correct it.

The Solution: Use Parentheses ()

To ensure your conditions are evaluated in the order you intend, you should always use parentheses to group OR conditions together. Parentheses override the default operator precedence.

The correct query for our example would be:

-- This is the correct way
SELECT *
FROM products
WHERE (category_id = 1 OR category_id = 2) AND unit_price > 15;

Now, the database understands the logic as:
"Find all products where (category_id is 1 OR 2)... AND... the unit_price is greater than 15."

This connection to mathematical order of operations is a powerful mental model. To explore this idea further, I recommend reading this short article.

Best Practice: Even when your query logic might work without them, use parentheses. It makes your queries unambiguous and far easier for you and others to read and maintain.

SELECT *
FROM support_tickets
WHERE (priority = 'High' OR priority = 'Medium') AND agent_id = 5;

For a comprehensive review of these concepts, including syntax and best practices, the following resource is very helpful.

Conclusion

You've now learned how to create much more sophisticated filters in your queries. By combining conditions with AND and OR, you can pinpoint data with much greater precision.

Key Takeaways:

• The AND operator requires all conditions to be true.
• The OR operator requires at least one condition to be true.
• SQL has an operator precedence rule: AND is evaluated before OR.
• Using parentheses () is the best practice to control the order of evaluation and make your query logic clear and bug-free.

Preview of the Next Lesson:
While AND and OR are powerful, they can sometimes be clumsy. For example, checking if a category is one of five different values would require four OR statements. In the next lesson, we'll explore more elegant and efficient ways to filter data using operators like IN (for checking against a list of values), BETWEEN (for checking a range), and LIKE (for flexible text pattern matching).