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

By

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.

Cheat sheets