SQL Filtering Essentials

For timestamp ranges, prefer a half-open interval such as >= '2025-12-01' AND < '2026-01-01' so fractional seconds are not missed. NOT IN can evaluate to UNKNOWN when NULL is present.

Handling Real-World Data with Smarter Filters

1. Introduction

At this stage, basic SQL filtering is no longer enough.

So far, we have learned how to:

• select rows
• compare exact values
• apply simple filters using WHERE

That is a strong foundation.

But real-world data is rarely clean.

In production systems, data is often:

• incomplete
• inconsistent
• messy
• partially missing
• irregularly formatted

This is where SQL’s filtering essentials become important.

These operators help handle practical, messy data:

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

These are some of the most frequently used operators in real SQL work.

2. Why These Operators Matter

In real databases:

• some values are missing
• some text fields are blank
• some values belong to a known list
• some records fall inside a date or number range
• some text follows a pattern instead of an exact value

Simple = and != are not enough for these cases.

That is why SQL provides specialized filtering operators.

Think of them as smarter versions of WHERE.

3. Real-World Dataset — users

To understand these operators properly, we need realistic messy data.

We will use a users table that intentionally contains:

• NULL values
• empty strings
• whitespace-only values
• missing purchases
• missing coupon codes
• multiple cities
• different email patterns

This is exactly how production data usually looks.

4. Table Setup

CREATE TABLE users (
  id                INT PRIMARY KEY AUTO_INCREMENT,
  email             VARCHAR(120) NOT NULL,
  full_name         VARCHAR(80)  NOT NULL,
  city              VARCHAR(60)  NULL,
  signup_at_utc     DATETIME     NOT NULL,
  last_purchase_inr DECIMAL(10,2) NULL,
  last_coupon_code  VARCHAR(30)  NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1
);

5. Why This Dataset Is Useful

This dataset is intentionally messy.

It contains real-world edge cases:

• city = NULL
• city = ''
• city = ' ' (space only)
• last_purchase_inr = NULL
• last_coupon_code = NULL

This is exactly why filtering essentials matter.

6. IS NULL

Definition

IS NULL checks whether a column has no value.

This means the value is unknown, missing, or not recorded.

Important Rule

NULL does not mean:

• zero
• empty string
• blank space

NULL means:

no value exists

Query

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

Output

Mental Model

Use IS NULL when asking:

Which rows have missing data?

7. Why = NULL Does Not Work

This is one of the most important SQL rules.

Wrong:

WHERE city = NULL

This never works.

Because NULL is unknown.

Unknown cannot be compared using =.

Always use:

WHERE city IS NULL

8. IS NOT NULL

Definition

IS NOT NULL checks whether a column has some value present.

It does not mean the value is clean.

It only means the value is not SQL NULL.

Query

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

Important Note

This includes:

• valid cities
• empty strings
• spaces

Because they are still not NULL.

That is why IS NOT NULL alone is often not enough in production.

9. Real-World Missing Data Problem

In real systems, “missing” data can appear in multiple forms:

• NULL
• ''
• ' ' (spaces)
• placeholders like N/A

So production systems often normalize values first.

Production-Safe Pattern

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

Why This Works

Step by step:

• TRIM(city) removes spaces
• NULLIF(..., '') converts empty string to NULL
• IS NOT NULL keeps only real values

This is how professionals clean dirty string data.

10. IN

Definition

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

It is cleaner than writing multiple OR conditions.

Query

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

Mental Model

IN means:

Is this value one of these options?

Why It Is Better Than OR

Instead of:

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

Use:

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

Cleaner and easier to read.

11. NOT IN

Definition

NOT IN checks whether a value matches none of the listed values.

Query

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

Mental Model

NOT IN means:

Show rows except these values.

Important Warning

If NULL is involved, NOT IN can behave unexpectedly.

Always be careful when NULLs are present.

12. BETWEEN

Definition

BETWEEN checks whether a value falls inside a range.

It includes both boundaries.

Query

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

Mental Model

BETWEEN means:

Is this value inside this range (inclusive)?

Important Rule

BETWEEN includes:

• start value
• end value

Both are included.

13. NOT BETWEEN

Definition

NOT BETWEEN checks whether a value lies outside the range.

Query

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

Mental Model

NOT BETWEEN means:

Show rows outside this range.

14. LIKE

Definition

LIKE is used for pattern matching in text.

It is used when exact match is not possible.

Wildcards

15. LIKE with %

Query

SELECT id, email
FROM users
WHERE email LIKE '%@tuf.com';

Meaning

Find emails ending with @tuf.com

Mental Model

% means:

anything can appear here

16. LIKE with Prefix Search

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

Find emails starting with test.

17. LIKE with _

SELECT id, email
FROM users
WHERE email LIKE 'test_user%';

Here _ means exactly one character.

This can match more than expected.

To match actual underscore, escape it.

Exact Underscore Match

SELECT id, email
FROM users
WHERE email LIKE 'test\_user%';

Now _ is treated as a real underscore.

18. NOT LIKE

Definition

NOT LIKE excludes text matching a pattern.

Query

SELECT id, email
FROM users
WHERE email NOT LIKE '%@tuf.com';

Mental Model

NOT LIKE means:

Exclude rows matching this text pattern.

19. Common Mistakes to Avoid

1. Using = NULL

Wrong:

WHERE city = NULL

Correct:

WHERE city IS NULL

2. Assuming IS NOT NULL Means Clean Data

It only means not SQL NULL.

It can still be:

• empty
• spaces
• junk

3. Using Long OR Instead of IN

Bad:

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

Better:

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

4. Forgetting BETWEEN Is Inclusive

BETWEEN a AND b includes both a and b.

5. Forgetting Wildcards in LIKE

Without %, LIKE behaves almost like exact match.

20. Summary

Filtering essentials help SQL handle real-world messy data.

They solve practical filtering problems:

• missing values
• list matching
• range filtering
• text pattern matching

These operators are foundational for real SQL work.

Final Mental Model

Use:

• IS NULL → missing values
• IN → list matching
• BETWEEN → range filtering
• LIKE → pattern matching

These are the tools that make SQL practical in production.