Often you do not want every row. You may need users from Tokyo, products above 50, or orders after a date. SQL uses WHERE to add those filters.
The condition below reads “keep only rows whose city equals Tokyo.” The database finds users, then decides which rows enter the final result set.
SELECT *
FROM users
WHERE city = 'Tokyo';| id | name | city | |
|---|---|---|---|
| 1 | Alice | alice@example.com | Tokyo |
| 3 | Chris | chris@example.com | Tokyo |
Comparing numbers
WHERE commonly compares numbers too. price > 50 keeps Keyboard and Backpack. Use price <= 40 for prices at most 40, or the widely supported SQL form category <> 'Electronics' to exclude Electronics. Many systems also accept !=.
= — equal
> < — greater / less
>= <= — include boundary
<> — not equal
Strings usually need quotes
Text values normally use single quotes, as in city = 'Tokyo'; numbers usually do not, as in price > 50. Quotes tell the database that Alice is text, not a column or keyword.
AND and OR
AND requires every condition to be true. OR needs any one condition. A price above 20 and below 70 returns Mouse and Backpack; a city of Tokyo or Osaka returns three users.
| Operator | Pass condition |
|---|---|
AND | Every condition is true, like passing two gates in sequence |
OR | At least one condition is true, like taking either open route |
SELECT *
FROM products
WHERE price > 20
AND price < 70;Be explicit when mixing AND and OR
AND normally has higher precedence than OR, so do not rely on left-to-right intuition. Use parentheses to express the requirement: (city = 'Tokyo' OR city = 'Osaka') AND id > 1. Explicit grouping is easier to read and maintain.
Using NOT
NOT reverses a condition. WHERE NOT city = 'Tokyo' returns users outside Tokyo. Later it will combine with IN, LIKE, and NULL tests.
The practical skill is translating a requirement into logic. “At least 40 but no more than 80” becomes price >= 40 AND price <= 80; “Tokyo or Kyoto” becomes two city conditions joined by OR.
Pass rows through condition gates
Edit SELECT + WHERE. Try comparison operators and two conditions joined by AND or OR, then inspect which rows pass.
| id | name | city | |
|---|---|---|---|
| 1 | Alice | [email protected] | Tokyo |
| 2 | Bob | [email protected] | Osaka |
| 3 | Chris | [email protected] | Tokyo |
| 4 | Diana | [email protected] | Kyoto |
Sandbox ready; no real database is connected.KNOWLEDGE QUIZ
Answer business questions with two conditions
Write two queries: (1) return name and price for products priced at least 40 and at most 80; (2) return name and city for users in Tokyo or Kyoto. Acceptance criteria: exactly two conditions each, AND for the first, OR for the second, and quotes around text values.
Optional: portable schema and data
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;
CREATE TABLE users (id INTEGER, name VARCHAR(100), email VARCHAR(200), city VARCHAR(100));
CREATE TABLE products (id INTEGER, name VARCHAR(100), price DECIMAL(10, 2), category VARCHAR(50));
INSERT INTO users VALUES
(1, 'Alice', '[email protected]', 'Tokyo'),
(2, 'Bob', '[email protected]', 'Osaka'),
(3, 'Chris', '[email protected]', 'Tokyo'),
(4, 'Diana', '[email protected]', 'Kyoto');
INSERT INTO products VALUES
(1, 'Keyboard', 80.00, 'Electronics'),
(2, 'Mouse', 40.00, 'Electronics'),
(3, 'Coffee Mug', 15.00, 'Home'),
(4, 'Backpack', 60.00, 'Fashion');