Lesson 0004: Filtering Essentials

Lesson 0004 — IS NULL, IN, BETWEEN, LIKE & Real-World Filtering — ~16 min read

At this stage you can SELECT rows and filter with = and AND/OR. In production, data is rarely complete: missing values, empty strings, ranges, sets, and patterns. This lesson covers IS NULL / IS NOT NULL, IN / NOT IN, BETWEEN / NOT BETWEEN, and LIKE / NOT LIKE.

The Dataset — Messy users

These operators only make sense when data is messy. We will use a realistic users table with NULL, empty strings, and spaces deliberately mixed in.

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
);

CREATE INDEX idx_users_email     ON users(email);
CREATE INDEX idx_users_signup_at ON users(signup_at_utc);
CREATE INDEX idx_users_city      ON users(city);

INSERT INTO users
(email, full_name, city, signup_at_utc, last_purchase_inr, last_coupon_code, is_active)
VALUES
('raj@tuf.com',            'Raj',            'Bengaluru', '2025-12-01 00:00:00',  999.00, 'WELCOME10',    1),
('test_user1@gmail.com',   'Test User One',  'Delhi',     '2025-12-05 10:00:00',  499.00, NULL,          1),
('testXuser2@gmail.com',   'Test User Two',  'Delhi',     '2025-12-10 12:00:00',  750.00, 'WELCOME_2026', 1),
('aayush@company.com',     'Aayush',         NULL,        '2025-11-30 23:59:59',  NULL,   NULL,          1),
('neha@example.com',       'Neha',           '',          '2025-12-31 23:59:59', 1500.00, 'TUF_50',       1),
('mohit@gmail.com',        'Mohit',          'Mumbai',    '2026-01-01 00:00:00',  299.00, 'NEWYEAR10',    1),
('sara@tuf.com',           'Sara',           'Bengaluru', '2025-10-10 05:00:00', 2000.00, NULL,          1),
('arjun@yahoo.com',        'Arjun',          'Pune',      '2025-12-20 09:00:00',  799.00, 'FLASH_SALE',   0),
('john.doe@gmail.com',     'John Doe',       'Chennai',   '2025-12-05 18:30:00',  300.00, NULL,          1),
('jane_doe@gmail.com',     'Jane Doe',       'Chennai',   '2025-12-06 18:30:00', 1200.00, 'WELCOME_BACK', 1),
('support+trial@tuf.com',  'Support Trial',  'Gurugram',  '2025-12-07 10:00:00',  NULL,   NULL,          1),
('priya@outlook.com',      'Priya',          'Hyderabad', '2025-12-08 10:00:00',  999.00, 'WELCOME10',    1),
('sameer@rediffmail.com',  'Sameer',         NULL,        '2025-12-09 10:00:00',  100.00, NULL,          1),
('emptycity@demo.com',     'Empty City',     ' ',         '2025-12-10 10:00:00',  499.00, NULL,          1),
('khushi@gmail.com',       'Khushi',         'Delhi',     '2025-12-11 10:00:00',  500.00, 'REFERRAL5',    1),
('promo@demo.com',         'Promo',          'Mumbai',    '2025-12-25 00:00:00', 1499.00, 'TUF_50',       1),
('intern@tuf.com',         'Intern',         'Bengaluru', '2025-12-22 20:00:00',  899.00, 'WELCOME_BACK', 1),
('hello@sample.com',       'Hello',          'Delhi',     '2025-12-02 08:00:00',  NULL,   NULL,          1);

CREATE TABLE users (
  id                SERIAL PRIMARY KEY,
  email             VARCHAR(120) NOT NULL,
  full_name         VARCHAR(80)  NOT NULL,
  city              VARCHAR(60)  NULL,
  signup_at_utc     TIMESTAMP    NOT NULL,
  last_purchase_inr NUMERIC(10,2) NULL,
  last_coupon_code  VARCHAR(30)  NULL,
  is_active         SMALLINT     NOT NULL DEFAULT 1
);

CREATE INDEX idx_users_email     ON users(email);
CREATE INDEX idx_users_signup_at ON users(signup_at_utc);
CREATE INDEX idx_users_city      ON users(city);

-- same INSERT as SQL (DATETIME → TIMESTAMP)

IS NULL — Finding Missing Values

id	email	full_name	city	signup_at_utc	last_purchase_inr	last_coupon_code	is_active
1	raj@tuf.com	Raj	Bengaluru	2025-12-01 00:00:00	999.00	WELCOME10	1
2	test_user1@gmail.com	Test User One	Delhi	2025-12-05 10:00:00	499.00	NULL	1
3	testXuser2@gmail.com	Test User Two	Delhi	2025-12-10 12:00:00	750.00	WELCOME_2026	1
4	aayush@company.com	Aayush	NULL	2025-11-30 23:59:59	NULL	NULL	1
5	neha@example.com	Neha	''	2025-12-31 23:59:59	1500.00	TUF_50	1
6	mohit@gmail.com	Mohit	Mumbai	2026-01-01 00:00:00	299.00	NEWYEAR10	1
7	sara@tuf.com	Sara	Bengaluru	2025-10-10 05:00:00	2000.00	NULL	1
8	arjun@yahoo.com	Arjun	Pune	2025-12-20 09:00:00	799.00	FLASH_SALE	0
9	john.doe@gmail.com	John Doe	Chennai	2025-12-05 18:30:00	300.00	NULL	1
10	jane_doe@gmail.com	Jane Doe	Chennai	2025-12-06 18:30:00	1200.00	WELCOME_BACK	1
11	support+trial@tuf.com	Support Trial	Gurugram	2025-12-07 10:00:00	NULL	NULL	1
12	priya@outlook.com	Priya	Hyderabad	2025-12-08 10:00:00	999.00	WELCOME10	1
13	sameer@rediffmail.com	Sameer	NULL	2025-12-09 10:00:00	100.00	NULL	1
14	emptycity@demo.com	Empty City	' '	2025-12-10 10:00:00	499.00	NULL	1
15	khushi@gmail.com	Khushi	Delhi	2025-12-11 10:00:00	500.00	REFERRAL5	1
16	promo@demo.com	Promo	Mumbai	2025-12-25 00:00:00	1499.00	TUF_50	1
17	intern@tuf.com	Intern	Bengaluru	2025-12-22 20:00:00	899.00	WELCOME_BACK	1
18	hello@sample.com	Hello	Delhi	2025-12-02 08:00:00	NULL	NULL	1

Expression	Meaning
`NULL = ''`	UNKNOWN — not TRUE
`NULL = 0`	UNKNOWN — not TRUE
`NULL = ' '`	UNKNOWN — not TRUE
`NULL IS NULL`	TRUE

Key: NULL ≠ empty string, NULL ≠ 0, NULL ≠ space. NULL means “unknown / not recorded” — it cannot be compared with = or !=.

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

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

IS NOT NULL — Finding Present Values

id	email	city
4	aayush@company.com	NULL
13	sameer@rediffmail.com	NULL

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

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

id	email	city
1	raj@tuf.com	Bengaluru
2	test_user1@gmail.com	Delhi
3	testXuser2@gmail.com	Delhi
5	neha@example.com	''
6	mohit@gmail.com	Mumbai
7	sara@tuf.com	Bengaluru
8	arjun@yahoo.com	Pune
9	john.doe@gmail.com	Chennai
10	jane_doe@gmail.com	Chennai
11	support+trial@tuf.com	Gurugram
12	priya@outlook.com	Hyderabad
14	emptycity@demo.com	' '
15	khushi@gmail.com	Delhi
16	promo@demo.com	Mumbai
17	intern@tuf.com	Bengaluru
18	hello@sample.com	Delhi

Note: '' and ' ' are not NULL — they are present values (empty/space). Real missing data comes in many forms.

Normalizing Missing Values

In production, treat NULL, empty string, and spaces as “missing” by normalizing with TRIM + NULLIF:

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

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

id	email	city
1	raj@tuf.com	Bengaluru
2	test_user1@gmail.com	Delhi
3	testXuser2@gmail.com	Delhi
6	mohit@gmail.com	Mumbai
7	sara@tuf.com	Bengaluru
8	arjun@yahoo.com	Pune
9	john.doe@gmail.com	Chennai
10	jane_doe@gmail.com	Chennai
11	support+trial@tuf.com	Gurugram
12	priya@outlook.com	Hyderabad
15	khushi@gmail.com	Delhi
16	promo@demo.com	Mumbai
17	intern@tuf.com	Bengaluru
18	hello@sample.com	Delhi

TRIM(city) removes spaces, NULLIF(…, '') turns empty into NULL, then IS NOT NULL keeps only truly present cities.

IN — Membership in a List

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

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

NOT IN — Excluding a List

id	email	city
2	test_user1@gmail.com	Delhi
3	testXuser2@gmail.com	Delhi
6	mohit@gmail.com	Mumbai
15	khushi@gmail.com	Delhi
16	promo@demo.com	Mumbai
18	hello@sample.com	Delhi

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

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

id	email	city
1	raj@tuf.com	Bengaluru
5	neha@example.com	''
7	sara@tuf.com	Bengaluru
8	arjun@yahoo.com	Pune
9	john.doe@gmail.com	Chennai
10	jane_doe@gmail.com	Chennai
11	support+trial@tuf.com	Gurugram
12	priya@outlook.com	Hyderabad
14	emptycity@demo.com	' '
17	intern@tuf.com	Bengaluru

Pitfall: If city contains NULL, NOT IN can return 0 rows unexpectedly — because NULL comparisons are UNKNOWN. Prefer NOT IN only when the list and column are known non-NULL, or use NOT EXISTS.

BETWEEN — Inclusive Range

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

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

NOT BETWEEN — Outside the Range

id	email	signup_at_utc
1	raj@tuf.com	2025-12-01 00:00:00
2	test_user1@gmail.com	2025-12-05 10:00:00
3	testXuser2@gmail.com	2025-12-10 12:00:00
5	neha@example.com	2025-12-31 23:59:59
8	arjun@yahoo.com	2025-12-20 09:00:00
9	john.doe@gmail.com	2025-12-05 18:30:00
10	jane_doe@gmail.com	2025-12-06 18:30:00
11	support+trial@tuf.com	2025-12-07 10:00:00
12	priya@outlook.com	2025-12-08 10:00:00
13	sameer@rediffmail.com	2025-12-09 10:00:00
14	emptycity@demo.com	2025-12-10 10:00:00
15	khushi@gmail.com	2025-12-11 10:00:00
16	promo@demo.com	2025-12-25 00:00:00
17	intern@tuf.com	2025-12-22 20:00:00
18	hello@sample.com	2025-12-02 08:00:00

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

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

LIKE — Pattern Matching

id	email	signup_at_utc
4	aayush@company.com	2025-11-30 23:59:59
6	mohit@gmail.com	2026-01-01 00:00:00
7	sara@tuf.com	2025-10-10 05:00:00

Wildcard	Meaning	Example Pattern
`%`	Any number of characters (including 0)	`'%@tuf.com'` ends with @tuf.com
`_`	Exactly one character	`'test_user%'` → 9-char prefix

Ends with @tuf.com

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

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

id	email
1	raj@tuf.com
7	sara@tuf.com
11	support+trial@tuf.com
17	intern@tuf.com

Starts with test

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

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

id	email
2	test_user1@gmail.com
3	testXuser2@gmail.com

_ matches exactly one character

'test_user%' → 10 characters where the 5th character is _ wildcard (any one char), then user:

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

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

id	email
2	test_user1@gmail.com
3	testXuser2@gmail.com

Both match because _ matches _ in row 2 and X in row 3 — it is a wildcard, not a literal underscore.

Escaping _ for a literal underscore

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

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

NOT LIKE — Excluding Patterns

id	email
2	test_user1@gmail.com

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

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

Notes

id	email
2	test_user1@gmail.com
3	testXuser2@gmail.com
4	aayush@company.com
5	neha@example.com
6	mohit@gmail.com
8	arjun@yahoo.com
9	john.doe@gmail.com
10	jane_doe@gmail.com
12	priya@outlook.com
13	sameer@rediffmail.com
14	emptycity@demo.com
15	khushi@gmail.com
16	promo@demo.com
18	hello@sample.com

IS NULL / IS NOT NULL :-

NULL ≠ '' ≠ 0 ≠ ' ' — use IS NULL / IS NOT NULL, never = NULL

city IS NULL → ids 4,13; city IS NOT NULL → all except 4,13 (but includes '' and ' ')

Normalize: NULLIF(TRIM(city),'') IS NOT NULL → 14 true cities (excludes NULL/''/' ')

IN / BETWEEN :-

IN ('Delhi','Mumbai') → membership; NOT IN → outside list (NULL pitfall)

BETWEEN '2025-12-01' AND '2025-12-31' → inclusive range; NOT BETWEEN → outside

LIKE / NOT LIKE :-

% = any chars (0+); _ = exactly one char

'%@tuf.com' → 4 rows; 'test%' → 2 rows; 'test_user%' → both ( _ wildcard); 'test\_user%' → only literal _

NOT LIKE '%@tuf.com' → exclude pattern

Messy data pattern :-

Real city has NULL/''/' ' — IS NOT NULL alone not enough; combine TRIM + NULLIF

Prefer IN for sets, BETWEEN for ranges, LIKE for patterns with % and _

Questions? Ask your agent — you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.

Filtering Essentials

The Dataset — Messy `users`

IS NULL — Finding Missing Values

IS NOT NULL — Finding Present Values

Normalizing Missing Values

IN — Membership in a List

NOT IN — Excluding a List

BETWEEN — Inclusive Range

NOT BETWEEN — Outside the Range

LIKE — Pattern Matching

Ends with `@tuf.com`

Starts with `test`

`_` matches exactly one character

Escaping `_` for a literal underscore

NOT LIKE — Excluding Patterns

Notes

IS NULL / IS NOT NULL :-

IN / BETWEEN :-

LIKE / NOT LIKE :-

Messy data pattern :-

Filtering Essentials

The Dataset — Messy users

IS NULL — Finding Missing Values

IS NOT NULL — Finding Present Values

Normalizing Missing Values

IN — Membership in a List

NOT IN — Excluding a List

BETWEEN — Inclusive Range

NOT BETWEEN — Outside the Range

LIKE — Pattern Matching

Ends with @tuf.com

Starts with test

_ matches exactly one character

Escaping _ for a literal underscore

NOT LIKE — Excluding Patterns

Notes

IS NULL / IS NOT NULL :-

IN / BETWEEN :-

LIKE / NOT LIKE :-

Messy data pattern :-

The Dataset — Messy `users`

Ends with `@tuf.com`

Starts with `test`

`_` matches exactly one character

Escaping `_` for a literal underscore