Filtering Essentials in Detail

For timestamp ranges, prefer a half-open interval so fractional seconds are not missed. Pattern escaping is dialect-dependent; use an explicit ESCAPE clause when portability matters, and remember that negated predicates also exclude NULL through UNKNOWN.

At this stage, SQL becomes much more practical.

Until now, filtering mostly meant:

• exact match (=)
• comparison (>, <)
• basic logical checks (AND, OR)

But real production data is rarely clean.

In real databases:

• some values are missing
• some values are blank
• some values belong to a known list
• some values fall inside a range
• some values follow a text pattern instead of an exact value

This is where Filtering Essentials become important.

These operators help you work with real-world messy data:

• IS NULL / IS NOT NULL
• IN / NOT IN
• BETWEEN / NOT BETWEEN
• LIKE / NOT LIKE

These are some of the most important SQL operators you will use in production systems.

1. IS NULL

What is NULL?

NULL means:

the value is missing, unknown, or not recorded

This is one of the most misunderstood ideas in SQL.

NULL does not mean:

• not zero
• not empty string
• not space
• not false

It specifically means:

we do not have a value here

That is why SQL treats NULL differently from normal values.

Very Important Rule

You cannot compare NULL using:

• =
• !=
• <>

These do not work:

WHERE city = NULL
WHERE city != NULL

Because SQL cannot compare something against an unknown value.

That is why SQL gives special operators:

• IS NULL
• IS NOT NULL

Mental Model

Think of NULL like this:

A form asks for your city.

If the user never fills it:

• city is not Delhi
• city is not Mumbai
• city is not blank text

It is simply:

unknown

That is NULL.

Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NULL;

Example — Find users with no city recorded

SELECT
    id,
    email,
    city
FROM users
WHERE city IS NULL;

Output

Intuition

This query asks:

Show me users whose city was never recorded.

This is common in production when you need to find incomplete user profiles.

Real-Life Use Case

Find users who never used a coupon.

SELECT full_name, email
FROM users
WHERE last_coupon_code IS NULL;

Why this matters

This helps marketing teams identify:

• users who never redeemed offers
• users who may need re-engagement campaigns
• users who are eligible for first-time promotions

2. IS NOT NULL

IS NOT NULL means:

show rows where a value exists

This removes rows where the value is missing.

Syntax

SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;

Example — Find users with a city recorded

SELECT
    id,
    email,
    city
FROM users
WHERE city IS NOT NULL;

Intuition

This query asks:

Show me users where city exists in some form.

Important:

This includes:

• real city names
• empty strings ('')
• spaces (' ')

Because those are still values.

They are bad values, but not NULL.

That is a huge difference.

3. Cleaning Messy Data (Normalization)

This is where production SQL becomes real.

In real systems, missing values often appear as:

• NULL
• '' (empty string)
• ' ' (space)
• ' ' (multiple spaces)

All of these mean “missing” to a business.

But SQL treats them differently.

So before filtering, we often normalize the data.

Production-Safe Pattern

SELECT
    id,
    email,
    city
FROM users
WHERE NULLIF(TRIM(city), '') IS NOT NULL;

Step-by-step Breakdown

TRIM(city)

Removes leading and trailing spaces.

Examples:

• ' Delhi ' → 'Delhi'
• ' ' → ''

NULLIF(TRIM(city), '')

If the cleaned city becomes empty string, convert it to NULL.

Examples:

• 'Delhi' → 'Delhi'
• '' → NULL

IS NOT NULL

Now keep only real city values.

Intuition

This query means:

Show only users whose city has real usable text.

This is one of the most important real-world filtering patterns.

4. IN

IN checks whether a value matches any value from a list.

It is a cleaner version of multiple OR conditions.

Without IN

WHERE city = 'Delhi' OR city = 'Mumbai'

With IN

WHERE city IN ('Delhi', 'Mumbai')

Same result, much cleaner.

Example

SELECT
    id,
    email,
    city
FROM users
WHERE city IN ('Delhi', 'Mumbai');

Intuition

This asks:

Show users who belong to any of these cities.

Use IN when checking one column against many allowed values.

Real-Life Use Case

SELECT full_name, city
FROM users
WHERE city IN ('Bengaluru', 'Gurugram', 'Hyderabad');

This is useful for:

• regional analysis
• city targeting
• location segmentation

5. NOT IN

NOT IN means:

exclude rows matching any value in this list

Example

SELECT
    id,
    email,
    city
FROM users
WHERE city NOT IN ('Delhi', 'Mumbai');

Intuition

This asks:

Show users from everywhere except Delhi and Mumbai.

Useful for exclusions.

Very Important Trap — NULL with NOT IN

This is one of the most famous SQL traps.

WHERE city NOT IN ('Delhi', NULL)

This can return zero rows.

Why?

Because SQL now asks:

Is city not equal to Delhi and not equal to unknown?

And SQL cannot prove comparison against unknown.

So result becomes unknown.

Rule to Remember

Never allow NULL inside NOT IN.

This causes silent logical bugs.

6. BETWEEN

BETWEEN checks whether a value falls inside a range.

It is inclusive.

That means both boundaries are included.

Syntax

WHERE value BETWEEN start AND end

Equivalent to:

WHERE value >= start AND value <= end

Example — Date Range

SELECT
    id,
    email,
    signup_at_utc
FROM users
WHERE signup_at_utc BETWEEN '2025-12-01 00:00:00'
                        AND '2026-01-01';

Intuition

This asks:

Show users who signed up during December 2025.

Very common for:

• monthly reports
• date windows
• billing cycles

Numeric Example

SELECT full_name, last_purchase_inr
FROM users
WHERE last_purchase_inr BETWEEN 400 AND 800;

This asks:

Show mid-range spenders.

Useful for:

• pricing analysis
• customer segmentation
• spending bands

7. NOT BETWEEN

NOT BETWEEN means:

outside the range

Equivalent to:

WHERE value < start OR value > end

Example

SELECT full_name, last_purchase_inr
FROM users
WHERE last_purchase_inr NOT BETWEEN 300 AND 1200;

Intuition

This asks:

Show outliers (very low or very high spenders).

Very useful for:

• anomaly detection
• VIP users
• low-engagement users

8. LIKE

LIKE is used for pattern matching in text.

Use it when exact match is too strict.

Wildcards

%

Matches any number of characters

_

Matches exactly one character

Example

WHERE email LIKE '%@tuf.com'

Means:

ends with @tuf.com

Example

WHERE full_name LIKE 'J%e'

Means:

• starts with J
• ends with e
• anything in between

Intuition

LIKE is used when you know part of the text, not the full text.

Perfect for:

• search bars
• log filtering
• partial matches

Common Patterns

Starts with

LIKE 'A%'

Ends with

LIKE '%gmail.com'

Contains

LIKE '%demo%'

Exact one character

LIKE 'N___'

Matches exactly 4-letter names starting with N.

9. Escaping Wildcards

Sometimes your real data contains _ or %.

But SQL treats them as wildcards.

To search them literally, escape them.

WHERE email LIKE 'test\_user%'

This means:

underscore is real text, not wildcard

10. NOT LIKE

NOT LIKE excludes pattern matches.

Example

SELECT id, email, full_name
FROM users
WHERE email NOT LIKE 'test%';

Intuition

This asks:

Show all real users, exclude test accounts.

Very common in production when cleaning reports.

Common Mistakes to Avoid

1. NULL is not empty

• NULL ≠ ''
• NULL ≠ ' '

2. Never compare NULL with =

Wrong:

WHERE city = NULL

Correct:

WHERE city IS NULL

3. NOT IN + NULL is dangerous

Avoid NULL inside NOT IN.

4. BETWEEN is inclusive

Both start and end values are included.

5. Date ranges need time

'2025-12-31' may miss rows later that day.

Use full timestamps.

6. %text% is expensive

Leading % often prevents index usage and becomes slow.

Final Memory Trick

• IS NULL → missing
• IS NOT NULL → exists
• IN → one from list
• NOT IN → exclude list
• BETWEEN → inside range
• NOT BETWEEN → outside range
• LIKE → pattern match
• NOT LIKE → exclude pattern