Filtering Essentials
At this stage in learning SQL, most learners are comfortable
writing basic SELECT queries. They can retrieve rows from a table,
compare exact values, and apply simple filtering conditions.
These skills form a strong foundation, but they represent only the
beginning of working with real data.
In real-world databases, data is rarely complete or perfectly
structured. Some columns contain missing values. Some fields
are present but left empty. Certain values do not match a single
number or word but fall within a range. Others belong to a
predefined set of values or follow a recognizable pattern rather
than an exact match.
To handle these practical situations, SQL provides specialized
operators. These include IS NULL and IS NOT NULL for identifying
missing data, IN and NOT IN for checking membership in a list,
BETWEEN and NOT BETWEEN for working with ranges, and LIKE
and NOT LIKE for pattern-based matching.
Dataset
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(120) NOT NULL,
full_name VARCHAR(80) NOT NULL,
city VARCHAR(60) NULL, -- can be NULL /
empty / spaces
signup_at_utc DATETIME NOT NULL, -- stored as
UTC
last_purchase_inr DECIMAL(10,2) NULL, -- can be
NULL (never purchased)
last_coupon_code VARCHAR(30) NULL, -- can be
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@[Link]', 'Raj', 'Bengaluru', '2025-12-01 00:00:00', 999.00, 'WELCOME10', 1),
('test_user1@[Link]', 'Test User One', 'Delhi', '2025-12-05 10:00:00', 499.00, NULL, 1),
('testXuser2@[Link]', 'Test User Two', 'Delhi', '2025-12-10 12:00:00', 750.00, 'WELCOME_2026', 1),
('aayush@[Link]', 'Aayush', NULL, '2025-11-30 23:59:59', NULL, NULL, 1),
('neha@[Link]', 'Neha', '', '2025-12-31 23:59:59', 1500.00,'TUF_50', 1),
('mohit@[Link]', 'Mohit', 'Mumbai', '2026-01-01 00:00:00', 299.00, 'NEWYEAR10', 1),
('sara@[Link]', 'Sara', 'Bengaluru', '2025-10-10 05:00:00', 2000.00,NULL, 1),
('arjun@[Link]', 'Arjun', 'Pune', '2025-12-20 09:00:00', 799.00, 'FLASH_SALE', 0),
('[Link]@[Link]', 'John Doe', 'Chennai', '2025-12-05 18:30:00', 300.00, NULL, 1),
('jane_doe@[Link]', 'Jane Doe', 'Chennai', '2025-12-06 18:30:00', 1200.00,'WELCOME_BACK',1),
('support+trial@[Link]', 'Support Trial', 'Gurugram', '2025-12-07 10:00:00', NULL, NULL, 1),
('priya@[Link]', 'Priya', 'Hyderabad', '2025-12-08 10:00:00', 999.00, 'WELCOME10', 1),
('sameer@[Link]', 'Sameer', NULL, '2025-12-09 10:00:00', 100.00, NULL, 1),
('emptycity@[Link]', 'Empty City', ' ', '2025-12-10 10:00:00', 499.00, NULL, 1),
('khushi@[Link]', 'Khushi', 'Delhi', '2025-12-11 10:00:00', 500.00, 'REFERRAL5', 1),
('promo@[Link]', 'Promo', 'Mumbai', '2025-12-25 00:00:00', 1499.00,'TUF_50', 1),
('intern@[Link]', 'Intern', 'Bengaluru', '2025-12-22 20:00:00', 899.00, 'WELCOME_BACK', 1),
('hello@[Link]', 'Hello', 'Delhi', '2025-12-02 08:00:00', NULL, NULL, 1
);
Problem :- check city is equal to null and not null
Explain nullif operator
IS NOT NULL
IS NOT NULL checks whether a column’s value is
present (not NULL).
IN
IN checks if a value matches any one value from a list.
NOT IN
NOT IN checks if a value matches none of the
values in the list.
BETWEEN
BETWEEN checks whether a value lies inside a
range, including boundaries.
NOT BETWEEN
NOT BETWEEN checks if a value lies outside that
inclusive range.
LIKE
LIKE matches text patterns (useful for searching).
Wildcards:
1. % = any number of characters (including 0)
2. _ = exactly one character
NOT LIKE
NOT LIKE excludes text that matches the pattern.
Problems
Q) 584 find customer referee
Q) next questions are in Bristom