AND, OR, IN and BETWEEN

A WHERE with one condition answers simple questions. Real questions usually need several.

AND: every condition must be true

SELECT name, category, price
FROM products
WHERE category = 'Accessories' AND price < 40;

Only accessories that also cost under 40 qualify. The keyboard is an accessory, but at 89.99 it fails the second test.

OR: at least one condition must be true

SELECT name, city, country
FROM customers
WHERE country = 'India' OR country = 'Japan';

NOT: reverse a condition

SELECT name, country
FROM customers
WHERE NOT country = 'India';

WHERE NOT country = 'India' means the same as WHERE country <> 'India'.

Mixing AND and OR

AND is evaluated before OR, the way multiplication comes before addition. That catches everyone out at least once. Suppose you want Office or Audio products under 100:

-- wrong: this means Office (any price) OR (Audio under 100)
SELECT name, category, price
FROM products
WHERE category = 'Office' OR category = 'Audio' AND price < 100;

The 349 chair sneaks in. Parentheses say what you mean:

SELECT name, category, price
FROM products
WHERE (category = 'Office' OR category = 'Audio') AND price < 100;

IN: match any value in a list

A chain of ORs on the same column is shorter with IN:

SELECT name, country
FROM customers
WHERE country IN ('India', 'Japan', 'UK');

NOT IN excludes the list:

SELECT order_id, status
FROM orders
WHERE status NOT IN ('delivered', 'cancelled');

BETWEEN: a range

BETWEEN low AND high includes both ends:

SELECT name, price
FROM products
WHERE price BETWEEN 30 AND 100
ORDER BY price;

It works for dates written as YYYY-MM-DD too, because that format sorts correctly as text:

SELECT order_id, order_date, status
FROM orders
WHERE order_date BETWEEN '2024-06-10' AND '2024-06-20';