AND, OR and more conditions

Lesson 3 of 21 · Part 1 · Querying data · about 15 minutes

One condition often isn't enough: "electronics that cost less than 20", "stationery or kitchen things". After WHERE you can join several conditions together:

main.sql

1SELECT name, category, price2FROM products3WHERE category = 'Electronics' AND price < 20;

Output

name	category	price
USB cable	Electronics	8.50
Phone case	Electronics	15.00

2 rows

AND and OR

AND: the row stays only if the conditions on both sides are true.
OR: the row stays if either side is true.

Stationery and kitchen products:

main.sql

1SELECT name, category2FROM products3WHERE category = 'Stationery' OR category = 'Kitchen';

Output

name	category
Notebook	Stationery
Pens (10)	Stationery
Water bottle	Kitchen
Coffee mug	Kitchen

4 rows

A product can't be in two categories at once, so this needs OR. With AND you'd get no rows at all. Press Step to see how WHERE decides, one row at a time.

NOT

NOT turns the condition after it around: true becomes false and false becomes true. Products that aren't electronics:

main.sql

1SELECT name, category2FROM products3WHERE NOT category = 'Electronics';

Output

name	category
Notebook	Stationery
Pens (10)	Stationery
Backpack	Bags
Water bottle	Kitchen
Coffee mug	Kitchen
Tote bag	Bags

6 rows

Here it's the same as category <> 'Electronics'. NOT is used more with IN, BETWEEN and LIKE, coming up below.

Parentheses

When AND and OR are mixed, AND goes first, then OR, just as multiplication comes before addition in math. To find "electronics or kitchen products under 10", does this work?

Guess first

Will the headphones (electronics, 59.00) be in the result?

1SELECT name, category, price2FROM products3WHERE category = 'Electronics' OR category = 'Kitchen' AND price < 10;

AND goes first, so the query really means "electronics, or kitchen things under 10". The headphones are electronics, which makes the left side of OR true, so they stay; their price is never checked.

Put the OR in parentheses and it goes first:

main.sql

1SELECT name, category, price2FROM products3WHERE (category = 'Electronics' OR category = 'Kitchen') AND price < 10;

Output

name	category	price
USB cable	Electronics	8.50
Coffee mug	Kitchen	9.90

2 rows

Tip: Whenever AND and OR are mixed, add parentheses. Even when the order would be right anyway, the reader sees it at a glance.

IN: one of several values

Many ORs get long. IN takes a list of values in parentheses, and the condition is true if the column equals any of them:

main.sql

1SELECT name, city2FROM customers3WHERE city IN ('Paris', 'London', 'Sydney');

Output

name	city
Ana Silva	London
Ben Okafor	Paris
Chloe Martin	London
Farah Khan	Paris
Hana Sato	London
Julia Costa	Sydney

6 rows

That's the same as three city = … joined with OR. The other way around, NOT IN (…) means "equal to none of them".

BETWEEN: a range

BETWEEN low AND high finds a range, and both ends are included:

main.sql

1SELECT name, price2FROM products3WHERE price BETWEEN 9.90 AND 15;

Output

name	price
Water bottle	12.00
Coffee mug	9.90
Phone case	15.00
Tote bag	11.00

4 rows

The 9.90 coffee mug and the 15.00 phone case are both in. Dates work too: orders from the first half of October (October 15 included):

main.sql

1SELECT id, order_date2FROM orders3WHERE order_date BETWEEN '2025-10-01' AND '2025-10-15';

Output

id	order_date
5	2025-10-01
6	2025-10-03
7	2025-10-10
8	2025-10-12
9	2025-10-15

5 rows

LIKE: matching part of a text

= has to match exactly. When you know only part of the text, use LIKE with two special symbols:

Symbol	Meaning
`%`	any number of characters, zero included
`_`	exactly one character

Products whose name ends in "bag" (the backpack isn't one: it ends in "pack"):

main.sql

1SELECT name2FROM products3WHERE name LIKE '%bag';

Output

name
Tote bag

1 row

Customers with exactly 3 characters before the @ in their email (three _, then @ and %):

main.sql

1SELECT name, email2FROM customers3WHERE email LIKE '___@%';

Output

name	email
Ana Silva	[email protected]
Ben Okafor	[email protected]

2 rows

In MySQL, LIKE ignores the case of letters too; other databases may not (in PostgreSQL, a case-blind match is ILIKE).

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

Not too many, not too few

Get the products with a stock between 10 and 40 (both 10 and 40 count), with two columns: name and stock.

It should print

name	stock
Backpack	15
Water bottle	40
Headphones	10
Phone case	35
Tote bag	25

5 rows

exercise1.sql

1SELECT name, stock2FROM products;

Output

Press Run or Ctrl+Enter

Exercise 2

Fix it: parentheses

This should get the electronics and bags with a stock over 20, but the result has two extra rows. Fix it, with three columns: name, category, stock.

It should print

name	category	stock
USB cable	Electronics	200
Phone case	Electronics	35
Tote bag	Bags	25

3 rows

exercise2.sql

1SELECT name, category, stock2FROM products3WHERE category = 'Electronics' OR category = 'Bags' AND stock > 20;

Output

Press Run or Ctrl+Enter

Exercise 3

Four-letter emails

Get the customers with exactly 4 characters before the @ in their email, with two columns: name and email.

It should print

name	email
Emma Novak	[email protected]
Hana Sato	[email protected]

2 rows

exercise3.sql

1SELECT name, email2FROM customers;

Output

Press Run or Ctrl+Enter

What you learned

AND needs both sides true, OR needs either one, and NOT turns a condition around.
AND goes before OR; when they're mixed, add parentheses.
IN (value, value, …) matches any of them; NOT IN matches none of them.
BETWEEN low AND high finds a range with both ends included, for numbers and dates.
LIKE matches part of a text: % is any number of characters, _ exactly one.