Filtering Rows with WHERE and Logical Conditions

Why Filtering Matters

In real databases, tables often contain thousands or millions of rows. Most of the time you do not want all of them—you want the specific rows that match a rule: customers from a certain country, orders in a date range, products above a price, or records that are missing required information. Filtering is the job of the WHERE clause.

The WHERE clause limits which rows are returned (or affected) by a statement. You will use it most commonly with SELECT, but the same filtering logic also applies to UPDATE and DELETE. In this chapter you will focus on how to write correct, readable conditions using comparisons, logical operators, ranges, lists, pattern matching, and proper handling of NULL.

The Core Idea: WHERE Evaluates a Condition Per Row

Think of WHERE as a test applied to each row. For every row, the database evaluates the condition and decides whether the row should be included.

A condition is an expression that evaluates to true or false (and in SQL, sometimes “unknown” because of NULL). Only rows where the condition evaluates to true are returned.

SELECT * FROM customers WHERE country = 'Canada';

In this example, each row in customers is checked: if country equals 'Canada', the row is included; otherwise it is excluded.

Continue in our app.

You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

Or continue reading below...

Download the app

Comparison Operators

The simplest filters use comparison operators. These compare a column value to another value (a literal, a parameter, or another column).

• = equal
• <> or != not equal (support varies; <> is standard)
• >, >= greater than, greater than or equal
• <, <= less than, less than or equal

Examples with Numbers

SELECT product_id, name, price FROM products WHERE price >= 100;

SELECT order_id, customer_id, total_amount FROM orders WHERE total_amount < 50;

Examples with Text

SELECT customer_id, first_name, last_name FROM customers WHERE last_name = 'Nguyen';

Text comparisons depend on collation settings (case sensitivity and sorting rules). In some databases, 'nguyen' may match 'Nguyen'; in others it will not. If you need case-insensitive matching across systems, you may use functions like LOWER() on both sides, but be aware that functions can affect index usage.

SELECT customer_id, email FROM customers WHERE LOWER(email) = LOWER('Alice@Example.com');

Examples with Dates/Times

Date literal syntax varies by database, but the idea is the same: compare a date column to a date value.

SELECT order_id, order_date FROM orders WHERE order_date >= '2026-01-01';

When filtering by date ranges, be careful with time components. If order_date is a timestamp, '2026-01-01' might be interpreted as midnight at the start of the day. A safe pattern is to use a half-open interval: include the start, exclude the next day.

SELECT order_id, order_date FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';

Combining Conditions with AND, OR, and NOT

Real filters often require multiple rules. SQL provides logical operators to combine conditions.

• AND: both conditions must be true
• OR: at least one condition must be true
• NOT: negates a condition

AND: Narrowing Results

SELECT product_id, name, price, category FROM products WHERE category = 'Laptop' AND price <= 1500;

This returns laptops priced at 1500 or less. If either part is false, the row is excluded.

OR: Expanding Results

SELECT customer_id, country FROM customers WHERE country = 'Canada' OR country = 'Mexico';

This returns customers from either country.

NOT: Excluding Matches

SELECT order_id, status FROM orders WHERE NOT status = 'Cancelled';

Many people prefer the clearer form:

SELECT order_id, status FROM orders WHERE status <> 'Cancelled';

NOT becomes especially useful with more complex conditions (for example, negating a pattern match or a range).

Operator Precedence and Parentheses

When you mix AND and OR, the database follows precedence rules. Typically, NOT is evaluated first, then AND, then OR. If you do not use parentheses, you might get results you did not intend.

Example: you want customers in Canada who are either active or pending.

SELECT customer_id, country, status FROM customers WHERE country = 'Canada' AND (status = 'Active' OR status = 'Pending');

Without parentheses:

SELECT customer_id, country, status FROM customers WHERE country = 'Canada' AND status = 'Active' OR status = 'Pending';

This is interpreted as:

(country = 'Canada' AND status = 'Active') OR status = 'Pending'

That would include all pending customers from any country, which is not what you wanted. Parentheses make your intent explicit and protect you from mistakes.

Filtering for Ranges with BETWEEN (and Why You Should Be Careful)

BETWEEN checks whether a value falls within an inclusive range (both endpoints included).

SELECT product_id, name, price FROM products WHERE price BETWEEN 50 AND 100;

This includes prices of 50 and 100.

For dates and timestamps, inclusive endpoints can cause subtle bugs. If you write:

SELECT order_id, order_date FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

and order_date includes times, you might miss orders on 2026-01-31 after midnight unless your database interprets the end value as the end of the day (many do not). A safer approach is the half-open interval pattern:

SELECT order_id, order_date FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';

Filtering for Lists with IN

When you want to match against a set of discrete values, IN is clearer than multiple OR conditions.

SELECT customer_id, country FROM customers WHERE country IN ('Canada', 'Mexico', 'USA');

The equivalent with OR is longer and easier to mess up:

SELECT customer_id, country FROM customers WHERE country = 'Canada' OR country = 'Mexico' OR country = 'USA';

You can also use NOT IN to exclude a list:

SELECT product_id, category FROM products WHERE category NOT IN ('Clearance', 'Discontinued');

Be careful when the list (or subquery) can contain NULL. With NULL involved, NOT IN can behave unexpectedly because comparisons with NULL produce “unknown.” If you are using a subquery that might return NULL, consider filtering out NULL values inside the subquery or using NOT EXISTS (covered in more advanced querying topics).

Pattern Matching with LIKE

LIKE is used to match text patterns. It is especially useful for “starts with,” “ends with,” or “contains” searches.

Common wildcards:

• % matches any sequence of characters (including empty)
• _ matches exactly one character

Starts With

SELECT customer_id, last_name FROM customers WHERE last_name LIKE 'Sm%';

This matches Smith, Smythe, Small, etc.

Contains

SELECT product_id, name FROM products WHERE name LIKE '%wireless%';

This matches any product name containing “wireless” anywhere. Note that leading % often prevents the database from using an index efficiently, which can be slow on large tables.

Single-Character Match

SELECT employee_id, code FROM employees WHERE code LIKE 'A_7';

This matches codes like AB7 or AC7 but not A17 if the middle character is missing, and not AB17 because that has more characters.

Escaping Wildcards

If you need to search for a literal % or _ character, you must escape it. The exact syntax varies by database, but many support an ESCAPE clause.

SELECT note_id, text FROM notes WHERE text LIKE '%50\%%' ESCAPE '\';

This example searches for the literal string 50%.

Working with NULL: IS NULL and IS NOT NULL

NULL means “missing/unknown.” It is not the same as zero, an empty string, or false. A common beginner mistake is trying to compare NULL using =.

This does not work as intended:

SELECT customer_id, phone FROM customers WHERE phone = NULL;

Instead, use IS NULL:

SELECT customer_id, phone FROM customers WHERE phone IS NULL;

And to find rows where the value is present:

SELECT customer_id, phone FROM customers WHERE phone IS NOT NULL;

Why? Because phone = NULL is not true or false; it is “unknown.” SQL only returns rows where the condition is true.

Three-Valued Logic (True, False, Unknown)

SQL’s handling of NULL introduces a third logical result: unknown. This matters when you combine conditions.

Example: suppose discount_code can be NULL. Consider:

SELECT order_id FROM orders WHERE discount_code <> 'WELCOME';

If discount_code is NULL, the expression discount_code <> 'WELCOME' is unknown, not true—so the row is not returned. If you want “anything except WELCOME, including missing codes,” you must write it explicitly:

SELECT order_id FROM orders WHERE discount_code <> 'WELCOME' OR discount_code IS NULL;

Step-by-Step: Building a Complex WHERE Clause Safely

When conditions get longer, build them incrementally and verify results at each step. Here is a practical workflow using an imaginary orders table with columns order_id, order_date, status, total_amount, and sales_channel.

Goal

Find orders placed in January 2026, not cancelled, with totals between 50 and 500, and coming from either the Web or Mobile channel.

Step 1: Start with the date range

SELECT order_id, order_date, status, total_amount, sales_channel FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01';

Check that the returned dates are all in January.

Step 2: Exclude cancelled orders

SELECT order_id, order_date, status, total_amount, sales_channel FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01' AND status <> 'Cancelled';

If status can be NULL and you want to exclude only explicit cancellations, decide whether NULL should be included. If yes, add OR status IS NULL with parentheses:

SELECT order_id, order_date, status, total_amount, sales_channel FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01' AND (status <> 'Cancelled' OR status IS NULL);

Step 3: Add the total range

SELECT order_id, order_date, status, total_amount, sales_channel FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01' AND status <> 'Cancelled' AND total_amount BETWEEN 50 AND 500;

Because BETWEEN is inclusive, totals of exactly 50 and 500 are included. If you want to exclude 500, use total_amount >= 50 AND total_amount < 500.

Step 4: Add the channel list with IN

SELECT order_id, order_date, status, total_amount, sales_channel FROM orders WHERE order_date >= '2026-01-01' AND order_date < '2026-02-01' AND status <> 'Cancelled' AND total_amount BETWEEN 50 AND 500 AND sales_channel IN ('Web', 'Mobile');

This final query is readable because each condition is a small, clear rule. If you ever need to add an OR condition, use parentheses to keep the logic correct.

Common Filtering Mistakes and How to Avoid Them

1) Forgetting Parentheses with AND/OR

If you mix AND and OR, always add parentheses unless the logic is extremely simple and obvious. This prevents accidental broadening of results.

-- Intended: (A AND B) OR (A AND C)  (A is required in both cases) SELECT * FROM customers WHERE country = 'Canada' AND (status = 'Active' OR status = 'Pending');

2) Using = NULL Instead of IS NULL

Always use IS NULL and IS NOT NULL for missing values.

3) Using NOT IN with NULLs

If the list can contain NULL, NOT IN can filter out more than you expect. If you see “why did I get zero rows?” and you used NOT IN with a subquery, check for NULL values.

4) Filtering Timestamps with an Inclusive End Date

Prefer half-open intervals for timestamps: >= start and < next_period_start.

5) Pattern Matching That Is Too Broad

LIKE '%text%' is convenient but can be slow and can match unintended values. If you only need “starts with,” prefer LIKE 'text%'. If you need more advanced searching, databases offer full-text search features (outside the scope of this chapter).

Using WHERE with UPDATE and DELETE (Safety Mindset)

Filtering is not only for reading data. It is critical when modifying data. A missing or incorrect WHERE clause can update or delete far more rows than intended.

UPDATE with WHERE

UPDATE customers SET status = 'Inactive' WHERE last_login_date < '2025-01-01';

Before running an UPDATE, it is a good habit to run a SELECT with the same WHERE first to see which rows will be affected.

SELECT customer_id, status, last_login_date FROM customers WHERE last_login_date < '2025-01-01';

DELETE with WHERE

DELETE FROM sessions WHERE expires_at < '2026-01-01';

Again, preview with a SELECT first:

SELECT session_id, expires_at FROM sessions WHERE expires_at < '2026-01-01';

Practice Scenarios (Try Writing the WHERE Clause)

Scenario 1: Customers to Contact

You want customers who have an email address, are in either Canada or the USA, and whose status is Active.

SELECT customer_id, email, country, status FROM customers WHERE email IS NOT NULL AND country IN ('Canada', 'USA') AND status = 'Active';

Scenario 2: Products Needing Review

You want products where the name contains “refurb”, or the category is Clearance, but you only want items priced above 20.

Use parentheses so the price rule applies to both branches:

SELECT product_id, name, category, price FROM products WHERE price > 20 AND (name LIKE '%refurb%' OR category = 'Clearance');

Scenario 3: Orders with Missing Data

You want orders that are missing a shipping address or missing a shipping date.

SELECT order_id, shipping_address_id, shipped_at FROM orders WHERE shipping_address_id IS NULL OR shipped_at IS NULL;