SQL WHERE Explained: Filter Rows with AND, OR and IN

SQL WHERE helps you move from “show me everything” to “show me the records that match this question.” The difficult part is usually deciding what the conditions mean together. We will practise that decision with a small customer and order database.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Filter one table with one condition

Find customers whose city is Nairobi:

SELECT customer_id, name, city
FROM customers
WHERE city = 'Nairobi'
ORDER BY customer_id;

The result is Amina and Chao. Brian does not meet the condition. WHERE tests each input row. Rows that pass stay in the result; rows that do not pass are left out of this result. The stored records remain unchanged.

Use single quotes around text such as 'Nairobi'. Numbers such as 1000 can be written without quotes. The comparison operator = means equals. Other useful operators are >, <, >=, <= and <>, meaning greater than, less than, greater than or equal, less than or equal, and not equal.

Require both conditions with AND

Suppose you need orders worth at least 800 but less than 1500:

SELECT order_id, amount
FROM orders
WHERE amount >= 800 AND amount < 1500
ORDER BY order_id;

Orders 101 and 102 qualify. Order 103 is too small. Order 104 is exactly 1500, so it fails the strict less-than condition. Say the boundary out loud before writing it. “Up to and including 1500” would require <= 1500 instead.

AND narrows the result because every listed condition must pass for a row. It does not mean “find one row matching the first condition and a different row matching the second.” Both conditions apply to the same row.

Accept either condition with OR

Now find orders belonging to customer 2 or worth more than 1000:

SELECT order_id, customer_id, amount
FROM orders
WHERE customer_id = 2 OR amount > 1000
ORDER BY order_id;

This returns 101, 102 and 104. Order 102 qualifies through its customer even though its amount is only 800. A row matching both sides would still appear once. OR is a condition, not an instruction to duplicate records.

Use parentheses when mixing conditions

SQL evaluates AND before OR. Rather than relying on the reader to remember that, group the intended alternatives explicitly.

SELECT order_id, customer_id, amount
FROM orders
WHERE (customer_id = 1 OR customer_id = 2)
  AND amount >= 800
ORDER BY order_id;

The answer is orders 101 and 102. Without those parentheses, customer 1's order 103 would also pass because the amount rule would apply only to the customer 2 side. Parentheses are a simple way to make your business rule visible.

Shorten lists and ranges

customer_id IN (1, 2) is a readable replacement for the two customer equality conditions above. Do not write customer_id = 1 OR 2; each side of OR needs a complete condition unless you use IN.

amount BETWEEN 800 AND 1500 includes both ends. In this data it returns 101, 102 and 104. If the upper boundary must be excluded, write the two comparisons explicitly. This is especially useful later when working with date and time ranges.

NULL means missing or unknown information and needs special handling. An ordinary comparison with NULL does not behave like a comparison with a regular number. Use IS NULL or IS NOT NULL when checking missing values.

Try it yourself

Find orders belonging to customer 1 or customer 3, with an amount of at least 1000. Return the order number, customer number and amount. Before running the query, predict how many rows should pass.

SELECT order_id, customer_id, amount
FROM orders
WHERE customer_id IN (1, 3)
  AND amount >= 1000
ORDER BY order_id;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.