Using Multiple Condition Filters
Combine conditions in a WHERE clause with AND and OR, and use parentheses so they're checked the way you mean.
Apprentice · SQL · MySQL · Updated
When filtering data within a WHERE clause, you may need to filter for multiple conditions. This can be done with AND and OR keywords.
Combining Conditions with AND
The AND operator is used to combine multiple conditions in a WHERE clause, ensuring that all conditions must be true for a row to be included in the result set.
Finding Employees in a Specific City and with a Specific Job Title
SELECT * FROM employees
WHERE city = 'London' AND title = 'Sales Representative';
This query returns all employees who work in London and have the job title Sales Representative.
Finding Products within a Price Range That Are Still Sold
SELECT * FROM products
WHERE unit_price >= 50 AND unit_price <= 100 AND discontinued = FALSE;
This query returns all products that are priced between 50 and 100 (inclusive) and have not been discontinued.
Combining Conditions with OR
The OR operator is used to combine multiple conditions in a WHERE clause, ensuring that at least one of the conditions must be true for a row to be included in the result set.
Finding Customers from Specific Cities
SELECT * FROM customers
WHERE city = 'London' OR city = 'Madrid';
This query returns all customers who are located in either London or Madrid.
Finding Orders Shipped to a Specific Country or with a High Freight Cost
SELECT * FROM orders
WHERE ship_country = 'USA' OR freight > 500;
This query returns all orders that were either shipped to the USA or have a freight cost greater than 500.
Combining AND and OR
You can combine AND and OR operators in a single WHERE clause. When doing so, it is important to use parentheses to ensure the conditions are evaluated correctly.
Finding Employees with a Specific City and Job Title, or in a Different City
SELECT * FROM employees
WHERE (city = 'London' AND title = 'Sales Representative')
OR city = 'Seattle';
This query returns all employees who either work in London as a Sales Representative or work in Seattle.
Finding Products within a Price Range and Either in Stock or on Order
SELECT * FROM products
WHERE unit_price >= 50
AND unit_price <= 100
AND (units_in_stock > 0 OR units_on_order > 0);
This query returns all products that are priced between 50 and 100 (inclusive) and are either in stock or on order.
More Complex Example
Finding Customers Based on Multiple Criteria
SELECT * FROM customers
WHERE (city = 'London' OR city = 'Madrid')
AND (contact_title = 'Owner' OR contact_title = 'Sales Representative');
This query returns all customers who are either from London or Madrid and whose contact is either an Owner or a Sales Representative.
Do it: find the sauces and sweets running low
The buyer is restocking two categories: condiments (category 2) and confections (category 3). She wants the products in those two categories that have fewer than 20 left.
A teammate wrote the query in the box, but it lists one product too many: Chef Anton's Cajun Seasoning, which has 53 in stock.
The products table (the Tables tab shows the SQL that makes it) has these rows:
| product_name | category_id | units_in_stock |
|---|---|---|
| Chai | 1 | 39 |
| Chang | 1 | 17 |
| Aniseed Syrup | 2 | 13 |
| Chef Anton's Cajun Seasoning | 2 | 53 |
| Northwoods Cranberry Sauce | 2 | 6 |
| Pavlova | 3 | 29 |
| Sir Rodney's Scones | 3 | 3 |
| Teatime Chocolate Biscuits | 3 | 25 |
| Gumbär Gummibärchen | 3 | 15 |
Your query should give exactly these rows, in any order:
| product_name | category_id | units_in_stock |
|---|---|---|
| Aniseed Syrup | 2 | 13 |
| Northwoods Cranberry Sauce | 2 | 6 |
| Sir Rodney's Scones | 3 | 3 |
| Gumbär Gummibärchen | 3 | 15 |
Your job: fix the query's WHERE so it gives the buyer's list.
- Keep its three columns:
product_name,category_id,units_in_stock. - Keep a product only when it's in category 2 or category 3, and it has fewer than 20 in stock.
Practiced live on the lesson page: write the SQL right there and check it.