Filtering Data with WHERE, AND, OR, IN, BETWEEN in SQL

What You Will Learn

• The WHERE clause and how it filters rows
• Combine conditions with AND, OR, NOT
• Use IN, BETWEEN, LIKE, IS NULL
• The most dangerous beginner mistake: missing parentheses around OR

Why This Topic Matters

Every analysis starts by selecting the right rows. "Show me paid orders from Mumbai last month." "Show me customers who haven't ordered in 90 days." "Show me products priced between ₹500 and ₹2,000." All of these are WHERE clauses. Get this wrong and you analyze the wrong data — confidently.

Sample Table

We extend the orders table from lesson 17 with two more columns:

Simple WHERE — One Condition

SELECT *
FROM orders
WHERE city = 'Mumbai';

Returns rows 001, 004, 005. Single quotes for text values — never double quotes (those are for column names in standard SQL).

Operators you can use

AND — Both Conditions Must Be True

SELECT *
FROM orders
WHERE city = 'Mumbai'
  AND status = 'paid';

Returns rows 001 and 005. Both must be true. Row 004 (Mumbai, refunded) is excluded.

OR — Either Condition Can Be True

SELECT *
FROM orders
WHERE city = 'Mumbai'
   OR city = 'Chennai';

Returns rows 001, 002, 004, 005, 006. Either condition matches.

The Dangerous Trap: AND + OR Without Parentheses

Suppose you want: "paid orders from Mumbai OR Chennai." A beginner writes:

SELECT *
FROM orders
WHERE status = 'paid'
   AND city = 'Mumbai'
   OR city = 'Chennai';

This is wrong. SQL applies AND first, then OR. The query is interpreted as:

(status = 'paid' AND city = 'Mumbai') OR (city = 'Chennai')

So you get: all paid Mumbai orders + ALL Chennai orders (including pending and refunded ones). Not what you wanted.

The fix — always use parentheses

SELECT *
FROM orders
WHERE status = 'paid'
  AND (city = 'Mumbai' OR city = 'Chennai');

Now the OR is evaluated first, and you get paid orders from either city. Always parenthesize OR conditions when combined with AND.

IN — A Cleaner Way to OR

The previous query is cleaner with IN:

SELECT *
FROM orders
WHERE status = 'paid'
  AND city IN ('Mumbai', 'Chennai');

IN matches any value in the list. Read it as "city is one of these". Much more readable than chained ORs, especially when the list grows.

BETWEEN — Range Inclusive

SELECT *
FROM orders
WHERE amount BETWEEN 500 AND 1000;

Returns amounts 500, 750, 900. BETWEEN is inclusive on both ends — equivalent to amount >= 500 AND amount <= 1000.

Works on dates too:

SELECT *
FROM orders
WHERE ordered_at BETWEEN '2026-08-01' AND '2026-08-07';

Returns rows 001 through 004.

LIKE — Pattern Matching on Text

SELECT *
FROM orders
WHERE customer LIKE 'A%';

% matches any sequence of characters (including none). So 'A%' means "starts with A". Returns rows where customer starts with A: Anita's orders.

IS NULL — Testing for Missing Values

Row 006 has amount = NULL. You cannot write amount = NULL — NULL is not a value, it is the absence of one. The correct test:

SELECT *
FROM orders
WHERE amount IS NULL;

Returns row 006. To get the opposite — all rows with a real amount — use IS NOT NULL.

NOT — Negating a Condition

SELECT *
FROM orders
WHERE status NOT IN ('refunded', 'pending');

Returns only paid orders. NOT can prefix IN, LIKE, BETWEEN, IS NULL.

Combining Everything — Real-World Filter

"Paid orders from Mumbai or Chennai in the first week of August, with amount at least 500."

SELECT *
FROM orders
WHERE status = 'paid'
  AND city IN ('Mumbai', 'Chennai')
  AND ordered_at BETWEEN '2026-08-01' AND '2026-08-07'
  AND amount >= 500;

For our data: only row 001 (Anita, Mumbai, paid, ₹500, 2026-08-01) qualifies. The query expresses the exact business question in code.

Common Mistakes

1. Missing parentheses around OR. The most common and most dangerous SQL bug. Always parenthesize.
2. Using = NULL instead of IS NULL. The query returns zero rows even when NULLs exist.
3. Using double quotes for text values. "Mumbai" is treated as a column name in standard SQL. Use single quotes: 'Mumbai'.
4. Forgetting that BETWEEN is inclusive. If you want strict ranges, use >= ... AND < ... instead.
5. Putting WHERE before FROM. SQL has a fixed clause order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Practical Exercise (15 minutes)

Using the sample table above, write queries to return:

1. All orders placed by Anita.
2. All orders with amount greater than 800.
3. All paid orders.
4. All orders from Mumbai OR Chennai.
5. All paid orders from Mumbai OR Chennai (parenthesized correctly).
6. All orders with amount between 600 and 1200.
7. All orders where the customer's name starts with 'A'.
8. All orders where amount is missing (NULL).
9. All paid orders from Mumbai in the first week of August with amount ≥ 500.

Mini Challenge

Write a single query that returns all orders that are EITHER (paid AND from Mumbai) OR (refunded AND from Chennai). The parentheses matter. Confirm your query returns the rows you expect before moving on.

Key Takeaways