Filtering Precisely in SQL: SELECT, WHERE, IN, LIKE & BETWEEN for Interviews
How does one SQL line pull the exact 3,842 customers you care about from a table with millions of rows - without accidentally including last year’s inactive users or excluding today’s best leads? The difference is not “knowing SQL”; it is knowing how to filter with surgical precision.
SELECTchooses columns; it does not filter rows.WHEREfilters rows before the final selected output is displayed.INis clean for multiple exact values:city IN ('Delhi','Mumbai').LIKEis for text patterns:%means any length,_means one character.BETWEENis inclusive:BETWEEN 100 AND 200includes both 100 and 200.- SQL uses three-valued logic: conditions can be TRUE, FALSE or UNKNOWN, so handle
NULLwithIS NULL. - Always validate filters with row counts; a syntactically correct query can still be a wrong business answer.
Think of SQL filtering as a business question passing through a series of gates. The table holds everything, WHERE decides which rows survive, and SELECT decides what columns the user finally sees.
The Core Idea: Filtering Is Boolean Decision-Making
A SQL filter is not a vague instruction like “show good customers.” It is a Boolean test applied row by row. For every row, the database asks: does this condition evaluate to TRUE? If yes, keep it. If no, drop it. If the result is UNKNOWN because of NULL, the row is not returned by WHERE.
The basic pattern is:
SELECT column_1, column_2 FROM table_name WHERE condition;
For example:
SELECT customer_id, city, order_value FROM orders WHERE city = 'Pune';
This means: go to the orders table, keep only rows where city equals Pune, and display only three columns.
The Five Building Blocks You Must Know
Choosing the Right Operator: A 2x2 Matrix
Most wrong filters come from choosing the wrong operator. Before writing SQL, ask two questions: are you matching exact values or patterns, and are you checking one value or a range/list?
How Each Filter Works in Practice
SELECT: Choose the Output, Not the Rows
SELECT decides which columns or calculations appear in the result. If your table has 1,00,000 rows, SELECT customer_id can still return 1,00,000 rows. Row reduction happens through WHERE, JOIN, GROUP BY or other clauses.
Use SELECT * only for quick exploration. In interview answers and production-style queries, name the exact columns because it is clearer, faster to read and less error-prone.
WHERE: The Row Gate
WHERE is the main filtering clause. It can use comparison operators like =, <>, >, >=, <, <=, plus logical operators like AND, OR and NOT.
AND narrows the result because all conditions must be true. OR expands the result because any condition can be true. When both appear, use parentheses even if you know precedence. Parentheses make your business logic visible.
WHERE city = 'Delhi' AND revenue > 50000 is narrow. WHERE city = 'Delhi' OR revenue > 50000 is wider.
IN: Clean Multiple Exact Matches
Use IN when a column can equal any value from a list or a subquery.
WHERE city IN ('Delhi', 'Mumbai', 'Bengaluru')
This is easier to read than:
WHERE city = 'Delhi' OR city = 'Mumbai' OR city = 'Bengaluru'
Use NOT IN carefully when the list or subquery may contain NULL. In many SQL systems, NOT IN with a NULL can produce surprising results because comparisons involving NULL become UNKNOWN.
LIKE: Pattern Matching for Text
LIKE is for strings where exact equality is too rigid. Two wildcards matter:
%means zero or more characters._means exactly one character.
Two interview-level cautions: first, case sensitivity depends on the database and collation. Second, patterns beginning with % may be slower on large tables because the database cannot always use a normal index efficiently.
BETWEEN: Inclusive Ranges
BETWEEN is ideal for numeric, date or alphabetic ranges when you want both boundaries included.
WHERE order_value BETWEEN 500 AND 999
This means order_value >= 500 AND order_value <= 999.
The date-time trap: if created_at has timestamps, BETWEEN '2026-01-01' AND '2026-01-31' may miss records on 31 January after midnight depending on how the database interprets the boundary. For monthly timestamp filtering, prefer a half-open range:
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'
The Logical Order: Why Your Query Runs Differently Than It Reads
SQL is written starting with SELECT, but it is logically processed in a different order. For filtering questions, the most important sequence is FROM, then WHERE, then SELECT.
A Small Worked Example: Filtering Leads Step by Step
Assume a hypothetical leads table has 10,000 rows. The sales manager wants active PGDM leads from three cities, with test scores between 70 and 90, whose email address is from Gmail.
Final query:
SELECT lead_id, city, test_score, email FROM leads WHERE status = 'Active' AND program = 'PGDM' AND city IN ('Delhi','Mumbai','Bengaluru') AND test_score BETWEEN 70 AND 90 AND email LIKE '%@gmail.com';
The point is not the numbers; it is the habit. Add filters deliberately, check row counts, and confirm that each reduction matches business logic.
How to Check Whether Your Filter Is Good
A SQL filter is good when it returns the right business population, not merely when it runs without error. Use these checks while debugging or explaining your query.
Definitions
SELECT: SELECT returns chosen expressions or columns from the rows produced by the query.WHERE: WHERE keeps only rows whose search condition evaluates to TRUE.IN: IN tests whether a value equals any value in a list or subquery.LIKE: LIKE tests a character string against a pattern using wildcards.BETWEEN: BETWEEN tests whether a value lies inclusively between two boundary values.
Case Study: MakeMyTrip and the Business Value of Precise Filters
MakeMyTrip shows why precise filtering matters: travel users do not want all inventory, they want the few hotels or flights that match intent, timing, budget and constraints.
MakeMyTrip operates in a category where the user’s question is naturally filter-heavy: destination, check-in date, check-out date, guest count, price band, refundable status, star rating, amenities and location preference. A travel marketplace wins only when it can reduce a large inventory set into a small, relevant choice set without hiding viable options.
Think of this as a SQL lens for understanding the business logic, not a claim about MakeMyTrip’s proprietary database design. The primary driver is structured filtering that mirrors user intent. Supporting drivers include rich inventory attributes, real-time availability and pricing integrations, ranking logic, trust signals such as ratings, and a mobile UX that lets users refine without friction.
The lesson: filters are not a back-end technical detail. In digital businesses, precise filtering is part of product strategy because it shapes discovery, conversion and customer trust.
How AI Changes SQL Filtering
1. Natural-language SQL generation is becoming standard. In 2026, analysts increasingly ask tools to convert business questions into SQL drafts: “Show active users from Delhi or Mumbai who purchased in the last 30 days.” The risk is that the generated query may be syntactically clean but logically wrong, especially around NULL, dates and AND/OR precedence.
2. AI helps detect suspicious filters. Modern data tools can flag unusual row drops, missing date boundaries, inconsistent casing, or filters that exclude too many NULL values. This is valuable because the biggest SQL errors often look normal until the business number is checked.
3. AI makes semantic layers more important. As business users query data through chat interfaces, companies need governed definitions like “active customer,” “net revenue” and “eligible lead.” Without that layer, AI may generate five different filters for the same metric.
Use ChatGPT or Claude to generate three SQL versions for the same business question, then ask: “Explain how each WHERE condition changes the row set and identify risks around NULL, BETWEEN dates and AND/OR precedence.” Finally, verify the query manually with row counts.
Interview Relevance
“Write a SQL query to fetch customers from Delhi, Mumbai or Bengaluru whose order value is between ₹1,000 and ₹5,000 and whose email ends with gmail.com. Also explain the difference between WHERE, IN, LIKE and BETWEEN.”
A strong answer could be:
SELECT customer_id, city, order_value, email FROM customers WHERE city IN ('Delhi','Mumbai','Bengaluru') AND order_value BETWEEN 1000 AND 5000 AND email LIKE '%@gmail.com';
After writing the query, add one sentence of reasoning: “I used IN for multiple exact city values, BETWEEN for an inclusive numeric range, and LIKE because email domain matching is a text pattern.”
Common Mistake
The biggest mistake is writing filters as English sentences instead of Boolean logic - especially mixing AND and OR without parentheses. It costs candidates because the query may run but return a completely different population. One-line fix: put parentheses around every mixed condition and validate the row count after each filter.
What to Revise Next
Once row-level filtering is clear, move to summarising and combining data. That is the natural journey from “which rows?” to “what do they add up to?” and then “how do tables connect?”