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 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 not 3043 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 not 3043 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.