SQL WHERE Clause

Gaurav Shegekar from ByteCraftOct 21, 2026

SQL WHERE Clause

How to Find Exactly the Rows You Need

Oct 21

Imagine you’re building an e-commerce application with 100,000 products. A customer searches for headphones under ₹3,000, but your backend returns every product in the database. The frontend now has to deal with thousands of unnecessary records, and your customer is left wondering why finding one product takes so much work.

The problem isn’t necessarily your database. Your query might simply be missing one important thing: a WHERE clause. It’s one of the most useful parts of SQL because it allows you to filter records based on conditions. Let’s understand how it works with examples you’ll actually use when building backend applications.

What Is the WHERE Clause?

The WHERE clause filters rows based on a condition. When you execute a query, the database checks which rows satisfy that condition and returns only the matching rows. Rows that don’t satisfy it are excluded from the result. You can use WHERE with commands such as SELECT, UPDATE, and DELETE, making it important for both retrieving and modifying data.

Let’s use this products table for our examples.

product_idproduct_namepricecategorystock101Keyboard1500Accessories25102Mouse700Accessories50103Monitor12000Electronics10104Headphones2500Accessories0105Laptop65000Electronics8

Suppose you want to retrieve only products priced below ₹3,000. Instead of fetching the entire table and filtering the results in Java, you can ask the database to return only the matching records.

SELECT *
FROM products
WHERE price < 3000;

The result contains the Keyboard, Mouse, and Headphones. The Monitor and Laptop are excluded because their prices don’t satisfy the condition. Simple, right? Now let’s look at the operators and patterns that make WHERE useful in everyday development.

1. Comparison Operators

Comparison operators let you compare column values with specific values. You’ll commonly use =, !=, <>, >, <, >=, and <=. For example, = checks equality, while > checks whether a value is greater than another value. These operators are useful when filtering by price, user ID, account status, dates, or any other comparable value.

Suppose your application needs to display products costing at least ₹2,000.

SELECT product_name, price
FROM products
WHERE price >= 2000;

This returns the Monitor, Headphones, and Laptop. You can also use equality to find a specific category.

SELECT *
FROM products
WHERE category = 'Accessories';

One small detail worth remembering: SQL string values are generally written inside single quotes. Also, whether string comparisons are case-sensitive depends on the database and its collation or comparison rules, so don’t assume every database treats uppercase and lowercase identically.

2. Combine Conditions with AND and OR

Real applications rarely filter records using just one condition. Imagine your product page should display accessories that cost less than ₹3,000 and are available in stock. The AND operator lets you combine conditions, and a row must satisfy both conditions to be returned.

SELECT product_name, price
FROM products
WHERE category = 'Accessories'
  AND price < 3000
  AND stock > 0;

The result includes the Keyboard and Mouse, but not the Headphones because their stock is zero. Now suppose you want products from either the Accessories category or products priced above ₹50,000. That’s where OR comes in.

SELECT product_name, price
FROM products
WHERE category = 'Accessories'
   OR price > 50000;

This returns the accessories and the Laptop. Be careful when mixing AND and OR in one query because they have different precedence. Use parentheses when you want to make the intended logic clear, especially in queries involving several conditions.

3. Use IN Instead of Writing Too Many OR Conditions

Suppose an application needs to retrieve orders with the statuses PENDING, PROCESSING, or SHIPPED. You could write several conditions joined with OR, but the query becomes harder to read as the list grows. The IN operator provides a cleaner way to check whether a value matches any item in a list.

SELECT *
FROM orders
WHERE status IN ('PENDING', 'PROCESSING', 'SHIPPED');

This query returns orders whose status matches one of the listed values. It’s useful for filtering by several user IDs, product categories, cities, or order statuses. You can also use NOT IN to exclude a list of values, but pay attention when the list contains NULL, because SQL’s handling of unknown values can produce unexpected results.

4. Use BETWEEN for Ranges

Sometimes you need records within a particular range. For example, a reporting page might need products priced between ₹1,000 and ₹5,000. You could write price >= 1000 AND price <= 5000, but SQL provides BETWEEN to express this more compactly.

SELECT product_name, price
FROM products
WHERE price BETWEEN 1000 AND 5000;

BETWEEN includes both endpoints, so products priced exactly ₹1,000 or ₹5,000 also qualify. It’s useful for numeric ranges and date ranges, too. For dates, remember that the actual values and data types matter; when filtering timestamps for a calendar day, a half-open range from the start of the day to the start of the next day is often safer than trying to guess the last possible time of the day.

5. Find Text Using LIKE

Imagine you’re building a search feature where users can type part of a product name. They don’t always know the exact spelling or full name, so you need a way to match text patterns. The LIKE operator supports this using wildcards.

SELECT *
FROM products
WHERE product_name LIKE 'Head%';

The percent sign (%) matches zero or more characters, so this query finds product names beginning with Head. You can also use '%phone%' to find names containing phone, or '%top' to find names ending with top. The underscore (_) matches a single character. Case sensitivity depends on the database and its configuration, so check the behavior of the database you’re using.

6. The NULL Mistake That Trips Up Developers

Here’s a common mistake: trying to find records with missing values using column = NULL. It looks logical, but SQL doesn’t treat NULL as an ordinary value. It represents missing or unknown information, so comparisons such as = NULL don’t evaluate to true.

Use IS NULL instead.

SELECT *
FROM users
WHERE phone_number IS NULL;

This query returns users whose phone_number has no value recorded. To find users who do have a phone number, use IS NOT NULL. This distinction matters when working with optional fields, incomplete customer profiles, and database records that haven’t been fully populated.

7. WHERE Is Not Just for SELECT

So far, we’ve used WHERE to retrieve records, but it also helps control which rows get updated or deleted. For example, if you need to mark one order as shipped, you can target it by its unique ID rather than changing every order in the table.

UPDATE orders
SET status = 'SHIPPED'
WHERE order_id = 501;

Without the WHERE clause, this statement would update every row in orders. The same danger exists with DELETE: forgetting the condition can remove all rows from a table. Before running a destructive query, it’s a good habit to check the matching records with a SELECT query first, then execute the update or deletion inside an appropriate transaction when needed.

The Takeaway

The WHERE clause helps you work with the exact records your application needs. Comparison operators handle basic filtering, AND and OR combine conditions, IN simplifies matching lists, BETWEEN checks ranges, and LIKE searches text patterns. Learning to handle NULL correctly is just as important as learning the syntax.

For Java backend developers, these are everyday skills. Whether you’re writing SQL directly or using Spring Data JPA, understanding filtering helps you reason about which records your application retrieves or modifies. And remember: a query that returns the right results is the starting point; you should also consider indexes, query plans, and the size of the data when performance matters.

One question: Have you ever caused a bug because you forgot a WHERE condition—or spent time debugging a query that returned far too many rows? Share your experience in the comments. Someone else might learn from the mistake before making it in production.

Follow ByteCraft for practical SQL, Java, and backend development lessons.

Thanks for reading ByteCraft! Subscribe for free to receive new posts and support my work.

Pledge your support

ByteCraft is free today. But if you enjoyed this post, you can tell ByteCraft that their writing is valuable by pledging a future subscription. You won't be charged unless they enable payments.

Pledge your support