Filtering Data with Powerful Conditions

Beyond simple conditions

The WHERE clause becomes truly powerful when you combine conditions and use SQL's special filtering operators. Real questions are rarely "where city equals X" — they're "where the customer is from Mumbai and spent over 1000" or "where the name starts with A". This chapter gives you the tools for precise, real-world filtering.

Combining conditions with AND and OR

Use AND to require that all conditions are true, and OR to require that at least one is true:

-- Students from Ahmedabad AND older than 18 (both must be true)
SELECT * FROM students
WHERE city = 'Ahmedabad' AND age > 18;

-- Students from Mumbai OR Delhi (either one)
SELECT * FROM students
WHERE city = 'Mumbai' OR city = 'Delhi';

You can combine several, and use parentheses to control the logic clearly — just like in maths, parentheses group conditions so there's no ambiguity:

-- (from Mumbai OR Delhi) AND older than 18
SELECT * FROM students
WHERE (city = 'Mumbai' OR city = 'Delhi') AND age > 18;

Always use parentheses when mixing AND and OR — they make your intent explicit and prevent subtle logic bugs.

IN: matching a list of values

When you want to match any value from a list, IN is far cleaner than chaining many OR conditions:

-- Clunky with OR
SELECT * FROM students
WHERE city = 'Mumbai' OR city = 'Delhi' OR city = 'Pune';

-- Clean with IN — exactly the same result
SELECT * FROM students
WHERE city IN ('Mumbai', 'Delhi', 'Pune');

IN checks whether a column's value matches any value in the list. There's also NOT IN for "none of these". It's a small thing, but it makes queries much more readable when you're matching against several options.

BETWEEN: matching a range

For a range of values, BETWEEN is clearer than two separate comparisons. It's inclusive — both endpoints are included:

-- Students aged 18 to 20 (inclusive)
SELECT * FROM students
WHERE age BETWEEN 18 AND 20;

-- Same thing written out the long way
SELECT * FROM students
WHERE age >= 18 AND age <= 20;

BETWEEN works on numbers, dates, and even text. It's especially handy for date ranges — "orders between two dates" — which come up constantly in real reporting.

LIKE: pattern matching in text

Often you want to match text patterns rather than exact values — names starting with a letter, emails ending in a domain. LIKE does this using two wildcards: % matches any number of characters, and _ matches exactly one:

-- Names starting with 'M'
SELECT * FROM students WHERE name LIKE 'M%';

-- Names ending with 'a'
SELECT * FROM students WHERE name LIKE '%a';

-- Names containing 'eh' anywhere
SELECT * FROM students WHERE name LIKE '%eh%';

-- Emails from a specific domain
SELECT * FROM students WHERE email LIKE '%@gmail.com';

The % wildcard is the key: 'M%' means "M followed by anything", '%a' means "anything ending in a", and '%eh%' means "containing eh somewhere". LIKE is invaluable for search features and flexible text matching.

Handling NULL: the absence of a value

In databases, a missing or unknown value is represented by NULL — and it behaves specially. You can't check for it with =; you must use IS NULL or IS NOT NULL:

-- Find students with no email recorded
SELECT * FROM students WHERE email IS NULL;

-- Find students who DO have an email
SELECT * FROM students WHERE email IS NOT NULL;

-- This does NOT work as expected:
-- SELECT * FROM students WHERE email = NULL;   ❌ never matches anything

NULL means "no value / unknown", which is different from an empty string or zero. Because NULL represents the unknown, email = NULL doesn't work — you must use IS NULL. Handling NULLs correctly is a mark of someone who really understands SQL, and forgetting about them is a classic source of wrong results.

A real filtering query

Let's combine these tools into a realistic question — find recent customers from major cities whose names we can search:

SELECT name, city, age
FROM students
WHERE city IN ('Mumbai', 'Delhi', 'Ahmedabad')
  AND age BETWEEN 18 AND 25
  AND name LIKE 'A%'
  AND email IS NOT NULL
ORDER BY age;

This finds students from three specific cities, aged 18 to 25, whose names start with A, who have an email on file — sorted by age. Notice how each tool handles one part of the question, combining into a precise query. This expressive filtering is exactly what makes SQL so powerful for answering real business questions. Next, we'll learn to summarise and aggregate data — counting, totalling, and averaging.