AND, OR, IN and BETWEEN: combine conditions

Opening your lesson

One condition is rarely enough. Join conditions with AND when every one must be true, and with OR when any one will do:

WHERE category = 'Kitchen' AND price < 30

IN is a short way to write several ORs on one column: WHERE city IN ('Austin', 'Denver') means city = 'Austin' OR city = 'Denver'. BETWEEN picks a range and includes both ends: price BETWEEN 20 AND 40 is the same as price >= 20 AND price <= 40. Put NOT in front to flip either one: NOT IN, NOT BETWEEN.

When you mix AND and OR, SQL does the ANDs first, the way multiplication comes before addition. Use brackets to say what you mean: WHERE (city = 'Austin' OR city = 'Denver') AND loyalty_tier = 'Gold'. Without the brackets, that query returns every Austin customer plus the Gold ones in Denver.

SELECT first_name, city, loyalty_tier
FROM customers
WHERE city IN ('Austin', 'Denver')
  AND loyalty_tier = 'Gold';

IN matches a list; AND adds a second condition that must also be true.

Choose your character

Your Work Gym account