Filtering Rows with Advanced Conditions (BETWEEN, IN, NOT IN, AND, OR)
So far, we’ve learned how to filter rows using very simple comparisons like: - = (equal) - != (not equal) - (greater than) - < (less than) But SQL gives us more power
MSMuhammad SufiyanSoftware Engineer · 4d ago
Backend Engineering HubT-
So far, we’ve learned how to filter rows using very simple comparisons like:
= (equal)
!= (not equal)
> (greater than)
< (less than)
But SQL gives us more powerful operators that allow us to write cleaner and more expressive queries.
In this blog, we’ll cover:
BETWEEN
IN
NOT IN
Combining conditions using AND and OR
We’ll continue using our familiar cities table.
1️⃣ Using BETWEEN
The BETWEEN keyword allows us to check if a value lies between two numbers (inclusive).
Problem
We want to find cities whose area is between 2000 and 4000.
Looking at our data:
Delhi → area ≈ 2240
Sao Paulo → area ≈ 3043
So we expect Delhi and Sao Paulo in the result.
Query
SELECT name, area
FROM cities
WHERE area BETWEEN 2000 AND 4000;
Result
Delhi
Sao Paulo
📌 Important note
BETWEEN includes both boundary values.
So 2000 and 4000 are also counted.
Expanding the Range
If we increase the upper bound to 5000:
SELECT name, area
FROM cities
WHERE area BETWEEN 2000 AND 5000;
Now Shanghai also appears because its area is around 4015.
2️⃣ Using IN
The IN operator allows us to check if a value exists inside a list.
Problem
Fetch cities where the name is either Delhi or Shanghai.
Query
SELECT name, area
FROM cities
WHERE name IN ('Delhi', 'Shanghai');
Result
Delhi
Shanghai
This is much cleaner than writing multiple OR conditions.
3️⃣ Using NOT IN
NOT IN does the exact opposite.
Problem
Fetch cities except Delhi and Shanghai.
Query
SELECT name, area
FROM cities
WHERE name NOT IN ('Delhi', 'Shanghai');
Result
Tokyo
Sao Paulo
4️⃣ IN and NOT IN with Numbers
These operators also work perfectly with numbers, not just strings.
Example
Fetch cities whose area is not3043 or 8223.
SELECT name, area
FROM cities
WHERE area NOT IN (3043, 8223);
Result
Delhi
Shanghai
5️⃣ Combining Conditions with AND
Now let’s write compound conditions.
Problem
Find cities:
Whose area is not3043 or 8223
AND whose name is Delhi
Query
SELECT name, area
FROM cities
WHERE area NOT IN (3043, 8223)
AND name = 'Delhi';
Result
Delhi
📌 AND means both conditions must be true.
6️⃣ Combining Conditions with OR
Now let’s relax the condition.
Query
SELECT name, area
FROM cities
WHERE area NOT IN (3043, 8223)
OR name = 'Delhi';
Result
Delhi
Shanghai
📌 OR means any one condition can be true.
Adding More Conditions
We can chain as many conditions as we want:
SELECT name, area
FROM cities
WHERE area NOT IN (3043, 8223)
OR name = 'Delhi'
OR name = 'Tokyo';
Result
Delhi
Shanghai
Tokyo
🧠 Key Takeaways
BETWEEN → checks ranges
IN → checks presence in a list
NOT IN → excludes values
AND → all conditions must be true
OR → any condition can be true
You can combine multiple conditions to build powerful filters
🎯 Why This Matters
Real-world queries are never simple.
You’ll often need to answer questions like:
“Users from these cities but not those”
“Products between these prices”
“Orders with status X or Y but not Z”
And all of that starts with mastering WHERE conditions.
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
We’ll continue using our familiar