SQL WHERE: Filtering rows and combining conditions correctly

Below is an orders table with some columns. First, look at the data carefully.

Now let’s say we need orders with an amount greater than 100. Below is a SQL query for the same:

SELECT order_id, customer, amount
FROM orders
WHERE amount > 100;

WHERE amount > 100 describes which rows match the above condition. Conceptually, SQL checks that condition for each candidate row:

Since the condition matches only Asha’s and Carla’s orders. We got them in the Result.

The table below shows all the comparison operators we use in SQL. A table is provided to understand what a requirement might be and how that translates into comparison operations.

Note that the text values are used with single quotes, as in 'paid'. However, numeric values don’t require them.

A comparison expresses one decision. In the next section, we will see how to query multiple conditions.

Here is an order status table. Using this table, we will introduce multiple SQL conditions like AND and OR.

AND requires every condition to be TRUE

Use AND when you need to check multiple conditions at the same time. A row is included in the result only when all the conditions are satisfied.

SELECT order_id, country, status
FROM order_status
WHERE country = 'India'
  AND status = 'paid';

OR requires at least one of the conditions to be TRUE

Use OR when a row can match any one of several conditions. Unlike AND, which requires every condition to be true, OR includes a row even if only one condition is satisfied.

SELECT order_id, country, status
FROM order_status
WHERE status = 'paid'
   OR status = 'pending';

Now, let’s change the requirement to: Return paid or pending orders from India. Both statuses must be restricted to India.

We have written a query for this requirement. Now, can you predict what this should return?

SELECT order_id, country, status
FROM order_status
WHERE country = 'India'
  AND status = 'paid'
   OR status = 'pending';

When we have a list of values to match in a given column, IN makes the filter condition easier to read.

So, instead of:

WHERE status = 'paid'
   OR status = 'pending'
   OR status = 'processing'

we write using,

WHERE status IN ('paid', 'pending', 'processing')

Similarly, we can use NOT IN to exclude the listed values.

WHERE status NOT IN ('cancelled', 'refunded');

In SQL, the LIKE operator is used to search for a specific pattern in a string column. It is commonly used when you want to find records that start with, end with, or contain certain characters.

We will use the Customers table to explain the concepts here,

SQL LIKE uses two main wildcard characters:

Use Case:

1. Find names beginning with A:

SELECT customer_id, customer
FROM customers
WHERE customer LIKE 'A%';

The A is fixed; % allows any remaining characters. It can also match zero characters, so the name A would qualify if it existed.

Where you place % determines which values match.

LIKE 'an%'   -- begins with an
LIKE '%an'   -- ends with an
LIKE '%an%'  -- contains an anywhere

2. Match exactly one character with _

SELECT customer_id, customer
FROM customers
WHERE customer LIKE '_m%';

_ allows any first character, m fixes the second character, and % allows the rest. The result is Amit, customer 2.

Similarly, we can use LIKE with a NOT operator to exclude a pattern.

SELECT customer_id, customer
FROM customers
WHERE customer NOT LIKE 'A%';

WHERE code LIKE 'SAVE!_10' ESCAPE '!';

Pause and predict

-- Option A
WHERE customer LIKE 'm%'
 
-- Option B
WHERE customer LIKE '_m%'
 
-- Option C
WHERE customer LIKE '%_m'

The SQL BETWEEN operator filters values within a specified range, including both boundary values.

WHERE column_name BETWEEN lower_value AND upper_value

Let’s consider the ORDERS table

SELECT order_id, amount
FROM orders
WHERE amount BETWEEN 100 AND 300;

BETWEEN includes both endpoints. The condition means:

WHERE amount >= 100
  AND amount <= 300

Timestamp ranges

We will now use a timestamp filter. Lets see the Orders table below:

Here, if we use the condition created_at BETWEEN '2026-07-01' AND '2026-07-31', then it can miss Order Id 3. Because it will check BETWEEN 2026-07-01 09:00:00 and 2026-07-31 00:00:00.

To ensure we don’t miss any probable Ids, Use the start of the next month as the upper boundary:

SELECT order_id, created_at
FROM orders
WHERE created_at >= '2026-07-01'
  AND created_at <  '2026-08-01';

Let’s solve a problem that takes into consideration all the above concepts.

Solution:

First split the requirement one by one as follows:

1. Country must be India.
2. Order time must fall within July.
3. Status must be paid or pending.
4. Amount must be from 100 through 500, inclusive.

Then combine these into where clause and display the result as shown below:

SELECT
    order_id,
    customer,
    country,
    status,
    amount,
    created_at
FROM orders
WHERE country = 'India'
  AND created_at >= '2026-07-01'
  AND created_at <  '2026-08-01'
  AND status IN ('paid', 'pending')
  AND amount BETWEEN 100 AND 500;

In this section, we learned how to use the WHERE clause to filter rows using operators such as AND, OR, IN, NOT IN, LIKE, and BETWEEN.

We also explored how to combine conditions and avoid common filtering mistakes. With these fundamentals in place, we’re ready to move on to Pattern P02.