SQLQuerying Data with SELECTbeginner45 min read

Filtering Data with WHERE

Master the WHERE clause: filter rows using comparison and logical operators, handle NULL values and date ranges, and understand how predicate design can affect query performance.

On This Page (Table of Contents)

In this lesson, you will learn

✓Write conditional filter expressions using the WHERE clause
✓Evaluate equality and inequality operators on numbers, strings, and dates
✓Combine conditions using AND, OR, and NOT
✓Use IN, BETWEEN, and pattern matching to filter rows
✓Handle NULL values correctly using IS NULL and IS NOT NULL
✓Filter date and timestamp ranges accurately
✓Explain SARGability and how predicate design may affect index usage
✓Inspect query plans using EXPLAIN

How the WHERE Clause Filters Data

A table may contain thousands or millions of rows, but a query usually needs only some of them. The WHERE clause specifies the condition that a row must satisfy to be included in the result. A condition is also called a predicate. In the logical processing model, WHERE filters the rows produced by FROM and JOIN operations before grouping and final projection.

Filter Products by Category and Price

sql

1
2
3
4
SELECT product_id, title, category, price
FROM products
WHERE category = 'Electronics'
  AND price < 200.00;

Output

Only products in Electronics with a price below 200.00 are returned. The exact rows depend on the contents of products.

Both conditions must be TRUE. A product priced at exactly 200.00 does not qualify because the condition uses < rather than <=.

Understanding Comparison Operators

Comparison operators compare values and produce a truth value. They are used to filter numbers, text, dates, and other comparable values. The meaning of a comparison depends on the data types and, for text, the applicable collation.

Compare Values

sql

1
2
3
4
SELECT product_id, title, price
FROM products
WHERE price >= 50
  AND price <= 150;

Output

Products priced from 50 through 150, including both endpoints.

The two comparisons define an inclusive numeric range. The same operators can be used with compatible date and timestamp values.

Common SQL Comparison Operators

Operator	Meaning	Example
=	Equal to	price = 100
<>	Not equal to	status <> 'cancelled'
!=	Not equal to in PostgreSQL	status != 'cancelled'
>	Greater than	price > 100
<	Less than	price < 100
>=	Greater than or equal to	age >= 18
<=	Less than or equal to	stock_quantity <= 5

Using Equality and Inequality

Use a single equals sign to test equality in SQL. Programmers often type == out of habit from languages such as C++, JavaScript, and Python, but == is not the portable SQL equality operator. Use <> when testing whether two values differ.

Find Orders by Status

sql

1
2
3
4
5
6
7
8
9
-- Find pending orders
SELECT order_id, status
FROM orders
WHERE status = 'pending';
 
-- Find orders that are not cancelled
SELECT order_id, status
FROM orders
WHERE status <> 'cancelled';

Output

The first query returns pending orders. The second returns rows whose status is not cancelled, excluding rows where status is NULL.

A comparison involving NULL does not evaluate to TRUE or FALSE; it evaluates to UNKNOWN. As a result, a row with a NULL status does not pass either of these WHERE conditions.

Combining Conditions with AND

AND combines conditions that must all be true for a row to qualify. It is useful for narrowing results, such as finding products in a particular category that are also below a price limit.

Apply Multiple Requirements

sql

1
2
3
4
5
SELECT product_id, title, category, price
FROM products
WHERE category = 'Electronics'
  AND price < 100
  AND is_active = TRUE;

Output

Active electronics products with prices below 100.

Every condition connected by AND must evaluate to TRUE for the row to be returned.

Combining Conditions with OR

OR allows a row to qualify when at least one condition evaluates to TRUE. It can broaden a result to include multiple categories, statuses, or other alternatives.

Find Products from Either Category

sql

1
2
3
4
SELECT product_id, title, category
FROM products
WHERE category = 'Books'
   OR category = 'Electronics';

Output

Products whose category is Books or Electronics.

A row is included when either comparison is TRUE. For a list of alternatives on the same column, IN may be easier to read.

Using Parentheses with AND and OR

When AND and OR appear together, operator precedence affects the result. AND is evaluated before OR in standard SQL. Parentheses make the intended grouping explicit and help prevent accidental matches.

Group Conditions Explicitly

sql

1
2
3
4
5
6
7
SELECT product_id, title, category, price
FROM products
WHERE is_active = TRUE
  AND (
    category = 'Books'
    OR category = 'Electronics'
  );

Output

Active products in either Books or Electronics.

The parentheses group the category alternatives together. Without them, the query could include inactive electronics products because AND binds more tightly than OR.

Using NOT

NOT negates a condition. It is useful for reversing a boolean test, but remember that NOT UNKNOWN is still UNKNOWN. Therefore, NOT does not automatically include rows where a tested value is NULL.

Negate a Condition

sql

1
2
3
SELECT product_id, title, is_active
FROM products
WHERE NOT (is_active = TRUE);

Output

Rows whose is_active value is FALSE. Rows with is_active set to NULL are not returned.

For a non-null boolean column, NOT (is_active = TRUE) identifies inactive rows. If the column allows NULL, handle missing values separately when they should be included.

Filtering with IN

IN checks whether a value matches any member of a specified list. It is usually more readable than writing many OR comparisons for the same column.

Match Several Statuses

sql

1
2
3
SELECT order_id, status
FROM orders
WHERE status IN ('pending', 'processing', 'shipped');

Output

Orders whose status matches pending, processing, or shipped.

IN is useful when a column should match one of several permitted values. A NULL status still does not match this list.

Filtering with BETWEEN

BETWEEN checks whether a value falls within an inclusive range. Both the lower and upper endpoints are included. For timestamps, this matters because an upper endpoint representing midnight excludes the remainder of that day.

Find Products in a Price Range

sql

1
2
3
SELECT product_id, title, price
FROM products
WHERE price BETWEEN 50 AND 100;

Output

Products whose price is at least 50 and at most 100.

BETWEEN is equivalent to price >= 50 AND price <= 100 for ordinary non-null numeric values.

Filtering Text with LIKE and ILIKE

LIKE matches text against a pattern. The percent sign (%) matches zero or more characters, while the underscore (_) matches exactly one character. PostgreSQL also provides ILIKE for case-insensitive pattern matching according to its locale and collation behavior. ILIKE is PostgreSQL-specific rather than portable standard SQL.

Search Emails by Pattern

sql

1
2
3
4
5
6
7
8
9
-- Emails ending in example.com
SELECT user_id, email
FROM users
WHERE email LIKE '%@example.com';
 
-- PostgreSQL: case-insensitive pattern match
SELECT user_id, email
FROM users
WHERE email ILIKE '%@example.com';

Output

Rows whose email addresses match the requested suffix under the operator's matching rules.

The leading % permits any prefix. Pattern matching with a leading wildcard often cannot use a regular B-tree index for a simple prefix range scan, though specialized indexes or other plans may help.

Case Sensitivity in Text Comparisons

Do not assume every SQL database compares text identically. PostgreSQL text equality is generally case-sensitive, while collation and data type choices can affect text comparison behavior in other systems. LOWER can normalize text for comparison, and PostgreSQL supports ILIKE for case-insensitive pattern matching. For frequent case-insensitive lookups, consider an appropriate index strategy and verify the plan.

Compare Case-Insensitive Lookup Options

sql

1
2
3
4
5
6
7
8
-- Normalize both the stored value and comparison value
SELECT user_id, email
FROM users
WHERE LOWER(email) = LOWER('alex@example.com');
 
-- PostgreSQL expression index for repeated normalized lookups
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

Output

The SELECT returns matching rows according to the normalized comparison. The CREATE INDEX statement creates an expression index for that lookup pattern.

The index must match the expression used by the query for PostgreSQL to consider it. Index creation has storage and write costs, so use it when justified by workload and query plans.

Handling NULL Values Correctly

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or FALSE. Comparisons such as status = NULL do not test whether status is missing. Use IS NULL or IS NOT NULL instead.

Find Missing Values

sql

1
2
3
4
5
6
7
8
9
-- Find rows with no assigned manager
SELECT employee_id, full_name
FROM employees
WHERE manager_id IS NULL;
 
-- Find rows with an assigned manager
SELECT employee_id, full_name
FROM employees
WHERE manager_id IS NOT NULL;

Output

The first query returns employees without a manager_id. The second returns employees with a non-null manager_id.

IS NULL tests whether a value is missing. Ordinary equality and inequality comparisons with NULL evaluate to UNKNOWN.

The Three-Valued Logic of SQL

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN commonly arises when a comparison involves NULL. A WHERE clause retains only rows whose complete condition is TRUE. This explains why a row with a NULL status is not included by status <> 'cancelled'.

Include Missing Values Deliberately

sql

1
2
3
4
SELECT user_id, status
FROM users
WHERE status <> 'disabled'
   OR status IS NULL;

Output

Users whose status is not disabled, including users whose status is NULL.

The IS NULL condition explicitly includes missing statuses. Decide whether that is appropriate for the application before using this pattern.

How NULL Affects Common Conditions

Expression	Result when status is NULL	Included by WHERE?
status = 'active'	UNKNOWN	No
status <> 'active'	UNKNOWN	No
NOT (status = 'active')	UNKNOWN	No
status IS NULL	TRUE	Yes
status IS NOT NULL	FALSE	No

Filtering Boolean Columns

A PostgreSQL BOOLEAN column can store TRUE, FALSE, or NULL. You can compare it to TRUE or FALSE, or use the column directly as a condition. If NULL is allowed, it is neither TRUE nor FALSE and requires an explicit decision.

Find Active Products

sql

1
2
3
4
5
6
7
8
9
-- Explicit comparison
SELECT product_id, title
FROM products
WHERE is_active = TRUE;
 
-- Boolean expression directly
SELECT product_id, title
FROM products
WHERE is_active;

Output

Both queries return rows where is_active is TRUE.

The shorter boolean form is idiomatic PostgreSQL. Neither query returns rows where is_active is NULL.

Filtering Dates and Timestamps

Date filtering is safest when the intended boundaries are explicit. For a timestamp column, use a half-open interval: greater than or equal to the start and strictly less than the start of the next period. This includes every timestamp in the intended period without depending on fractional-second precision.

Find Orders from Calendar Year 2026

sql

1
2
3
4
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
  AND order_date <  TIMESTAMPTZ '2027-01-01 00:00:00+00';

Output

Orders whose order_date falls within the specified UTC interval, from the beginning of 2026 inclusive to the beginning of 2027 exclusive.

The explicit +00 offset defines UTC boundaries. If the business day or year is based on another time zone, construct the boundaries for that intended time zone instead.

SARGable Predicates and Indexes

SARGable means a predicate is suitable for using an index search strategy. For a common B-tree index, comparisons that constrain the indexed column directly can often support efficient equality or range lookups. Wrapping an indexed column in an expression may make a regular index less useful, but does not categorically prevent index use. An expression index or a different index type may support the query.

Inspect a Query Plan

sql

1
2
3
4
5
EXPLAIN
SELECT order_id, order_date
FROM orders
WHERE order_date >= TIMESTAMP '2026-01-01 00:00:00'
  AND order_date < TIMESTAMP '2027-01-01 00:00:00';

Output

PostgreSQL displays a planned execution strategy, which may include a sequential scan or an index scan depending on table statistics, available indexes, selectivity, and other factors.

EXPLAIN shows the plan selected by the optimizer. EXPLAIN ANALYZE also executes the statement and reports actual runtime information, so use it thoughtfully.

Direct range predicate vs Expression applied to column

Write predicates that match the available index strategy, then verify the actual plan rather than assuming one form is always faster.

Aspect	Direct range predicate	Expression applied to column
Timestamp filtering	created_at >= TIMESTAMP '2026-01-01 00:00:00' AND created_at < TIMESTAMP '2027-01-01 00:00:00'	EXTRACT(YEAR FROM created_at) = 2026
Common B-tree index	May support an efficient range scan on created_at	A normal created_at index may not directly support the EXTRACT expression
Possible alternative	Use an appropriate index on the original column	Consider an expression index if this exact expression is frequently queried

When to use Direct range predicate:

A direct range predicate is often a good default for timestamp range filtering.

When to use Expression applied to column:

Use an expression when it expresses the requirement clearly, and consider a matching index if workload evidence justifies it.

Common Filter Design Patterns

The following examples combine common filtering patterns. They assume the named columns exist in the relevant tables. Adapt the conditions to the data model and business rules of your application.

Practical WHERE Examples

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Products that need restocking
SELECT product_id, title, stock_quantity
FROM products
WHERE stock_quantity <= 5
  AND is_active = TRUE;
 
-- Orders in selected statuses
SELECT order_id, status, order_date
FROM orders
WHERE status IN ('pending', 'processing');
 
-- Customers whose email is missing
SELECT customer_id, full_name
FROM customers
WHERE email IS NULL;

Output

Three separate result sets: active products with low stock, orders in the selected statuses, and customers without an email.

Each query uses a predicate to describe which rows should qualify. Make sure the schema and business meaning support each condition.

MCQ 1: Equality

Test your understanding of SQL equality.

MCQ 2: AND

Check how AND combines predicates.

MCQ 3: OR

Check how OR combines predicates.

MCQ 4: NULL

Review the correct way to test for NULL.

MCQ 5: BETWEEN

Check the endpoints of BETWEEN.

MCQ 6: Pattern Matching

Review PostgreSQL pattern matching.

MCQ 7: Date Ranges

Check a reliable timestamp range pattern.

MCQ 8: Index Usage

Check your understanding of SARGability.

Guided Practice: Filter an Orders Table

Assume an orders table has order_id, customer_id, status, total_amount, and order_date. Write a query that returns order_id, customer_id, total_amount, and order_date for orders that are either pending or processing, have a total amount of at least 100, and were created during calendar year 2026 in UTC. Use explicit columns and a half-open timestamp range.

Practice Requirements

sql

1
2
3
4
5
6
7
-- Write a SELECT query that:
-- 1. Returns order_id, customer_id, total_amount, order_date.
-- 2. Includes pending OR processing orders.
-- 3. Requires total_amount >= 100.
-- 4. Limits order_date to calendar year 2026 in UTC.
-- 5. Uses explicit columns rather than SELECT *.
-- 6. Uses parentheses to make the status condition clear.

Output

A result containing qualifying orders with the four requested columns.

Group the status alternatives with parentheses, join them to the amount requirement with AND, and use an inclusive start plus an exclusive next-year boundary.

Guided Practice Solution

Compare your answer with this PostgreSQL solution. It groups the status condition, filters by amount, and uses an explicit UTC interval.

Orders Filter Solution

sql

1
2
3
4
5
6
7
8
9
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE (
    status = 'pending'
    OR status = 'processing'
  )
  AND total_amount >= 100
  AND order_date >= TIMESTAMPTZ '2026-01-01 00:00:00+00'
  AND order_date <  TIMESTAMPTZ '2027-01-01 00:00:00+00';

Output

Only pending or processing orders with total_amount >= 100 and order_date within the UTC year 2026.

Parentheses ensure the amount and date requirements apply to both allowed statuses.

Interview Questions and Answers

Use these questions to review filtering fundamentals, NULL behavior, date ranges, and index-aware predicate design.

Lesson Summary

WHERE filters rows using predicates. Comparison operators test values, AND and OR combine conditions, IN checks lists, BETWEEN checks inclusive ranges, and LIKE or PostgreSQL ILIKE match patterns. Use IS NULL and IS NOT NULL for missing values because SQL uses three-valued logic. For timestamps, half-open intervals are usually a reliable way to express calendar periods. Predicate design can affect index usage, but an index is not guaranteed to be selected; inspect the plan with EXPLAIN.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using double equals (==) for equality

Why it happens: Programmers familiar with C++, JavaScript, or Python carry their language's equality operator into SQL.

Incorrect Approach

sql

SELECT * FROM orders WHERE status == 'pending';

Correct Solution

sql

SELECT order_id, status
FROM orders
WHERE status = 'pending';

Key takeaway: Use a single equals sign for SQL equality comparisons.

#2 Using = NULL to test for missing values

Why it happens: NULL is mistakenly treated like an ordinary value.

Incorrect Approach

sql

SELECT employee_id FROM employees WHERE manager_id = NULL;

Correct Solution

sql

SELECT employee_id
FROM employees
WHERE manager_id IS NULL;

Key takeaway: Use IS NULL and IS NOT NULL. Comparisons with NULL generally evaluate to UNKNOWN.

#3 Mixing AND and OR without grouping

Why it happens: The written condition is interpreted according to operator precedence, which may differ from the developer's intention.

Incorrect Approach

sql

WHERE is_active = TRUE AND category = 'Books' OR category = 'Electronics'

Correct Solution

sql

WHERE is_active = TRUE
  AND (category = 'Books' OR category = 'Electronics')

Key takeaway: The corrected condition requires every returned product to be active and belong to one of the two categories.

#4 Assuming <> includes NULL values

Why it happens: A missing value is mistakenly treated as a value that differs from the comparison target.

Incorrect Approach

sql

SELECT user_id FROM users WHERE status <> 'disabled';

Correct Solution

sql

SELECT user_id
FROM users
WHERE status <> 'disabled'
   OR status IS NULL;

Key takeaway: The corrected version explicitly includes NULL statuses. Only do this if missing statuses should qualify for the result.

#5 Using an inclusive upper date boundary for a timestamp period

Why it happens: A date literal is used as if it represented every timestamp during that calendar day.

Incorrect Approach

sql

WHERE created_at BETWEEN TIMESTAMP '2026-01-01 00:00:00' AND TIMESTAMP '2026-12-31 00:00:00'

Correct Solution

sql

WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND created_at < TIMESTAMP '2027-01-01 00:00:00'

Key takeaway: The half-open interval includes the entire intended calendar year, regardless of fractional-second precision. Choose timestamp types and time-zone boundaries appropriate to the column.

#6 Assuming every function on a column forces a sequential scan

Why it happens: A regular B-tree index may not support a transformed expression, but the optimizer has other possible strategies.

Incorrect Approach

sql

Assuming WHERE LOWER(email) = 'alex@example.com' can never use an index.

Correct Solution

sql

CREATE INDEX idx_users_lower_email ON users (LOWER(email));
 
SELECT user_id, email
FROM users
WHERE LOWER(email) = 'alex@example.com';

Key takeaway: An expression index can support this specific expression. Whether PostgreSQL uses it depends on the query plan and data distribution.

#7 Assuming an available index guarantees a faster query

Why it happens: Indexes are often described as universally faster, without considering the number of qualifying rows or index maintenance costs.

Incorrect Approach

sql

Assuming every WHERE query must use an index scan.

Correct Solution

sql

EXPLAIN SELECT order_id, status FROM orders WHERE status = 'pending';

Key takeaway: A sequential scan can be cheaper when many rows qualify or a table is small. Use EXPLAIN to inspect the chosen plan.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Filtering Data with WHERE Knowledge Check

Test your understanding with immediate feedback

Question 1 of 8

Which operator is used for equality in SQL?

Question 2 of 8

When does a row pass a condition joined by AND?

Question 3 of 8

Which condition returns products that are either Books or Electronics?

Question 4 of 8

How should you test whether manager_id is missing?

Question 5 of 8

Is BETWEEN 10 AND 20 inclusive of 10 and 20?

Question 6 of 8

In a LIKE pattern, what does % represent?

Question 7 of 8

Which predicate is generally safest for selecting timestamps in a calendar year?

Question 8 of 8

What is the best way to determine whether PostgreSQL will use an index for a query?

Technical and placement interview questions

Frequently tested interview questions with detailed, full answers (click to expand)

Q1:What does WHERE do?

Answer:

WHERE filters rows according to a condition. Only rows for which the condition is TRUE pass the filter.

Q2:What is a predicate?

Answer:

A predicate is a condition that evaluates to TRUE, FALSE, or UNKNOWN in SQL.

Q3:Why does SQL use = instead of ==?

Answer:

The SQL equality operator is a single equals sign. The double-equals operator comes from many programming languages and is not the portable SQL equality operator.

Q4:What is the difference between <> and !=?

Answer:

Both mean not equal in PostgreSQL. The <> spelling is the standard SQL operator and is generally more portable.

Q5:What is the difference between AND and OR?

Answer:

AND requires both predicates to be TRUE for a TRUE result. OR is TRUE if at least one predicate is TRUE.

Q6:Why are parentheses important with AND and OR?

Answer:

AND has higher precedence than OR. Parentheses make the intended grouping explicit and prevent unintended rows from qualifying.

Q7:Why does status = NULL not work?

Answer:

A comparison involving NULL evaluates to UNKNOWN rather than testing for missingness. Use status IS NULL.

Q8:What is SQL three-valued logic?

Answer:

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN commonly occurs when a comparison involves NULL.

Q9:What does BETWEEN do?

Answer:

BETWEEN checks whether a value falls within an inclusive range, including both endpoints.

Q10:What are the wildcard characters in LIKE?

Answer:

Percent (%) matches zero or more characters, and underscore (_) matches exactly one character.

Q11:What is ILIKE?

Answer:

ILIKE is a PostgreSQL operator for case-insensitive pattern matching. It is not portable standard SQL.

Q12:What is SARGability?

Answer:

SARGability describes whether a predicate is suitable for an index search strategy. The best form depends on the available index and query requirements.

Q13:Does every function on a column prevent index usage?

Answer:

No. A regular index may not support the expression directly, but an expression index or another index strategy may support it.

Q14:Why use a half-open timestamp interval?

Answer:

Using >= the start and < the next period's start includes the full intended interval without relying on a particular timestamp precision.

Q15:How do you verify whether an index is used?

Answer:

Use EXPLAIN to inspect the planned execution strategy. EXPLAIN ANALYZE also executes the query and reports actual execution details.

Q16:Does PostgreSQL always use an available index?

Answer:

No. The optimizer chooses a plan based on factors such as estimated row counts, selectivity, table statistics, index costs, and the query itself.

Q17:What happens when a WHERE condition evaluates to UNKNOWN?

Answer:

The row is excluded because WHERE retains only rows for which the condition is TRUE.

Key takeaways

WHERE filters rows before later logical query operations such as grouping and projection.
Only rows whose WHERE condition evaluates to TRUE are included; FALSE and UNKNOWN rows are excluded.
SQL equality uses =, not the programming-language operator ==.
Use <> or != for inequality; <> is the standard SQL spelling.
Use IS NULL and IS NOT NULL to test missing values because comparisons with NULL evaluate to UNKNOWN.
AND requires both conditions to be TRUE, while OR requires at least one condition to be TRUE.
Parentheses make complex combinations of AND and OR easier to understand correctly.
BETWEEN includes both endpoints; date ranges that use timestamps often work best with a half-open interval.
Text comparison behavior depends on the data type, collation, and database configuration; do not assume every SQL database uses identical case-sensitivity rules.
A predicate that can use an index efficiently is often called SARGable, but index usage depends on the query plan and available indexes.
Functions on columns do not categorically prevent index usage; expression indexes and other plan strategies may support them.
Use EXPLAIN to inspect a query plan instead of assuming a query must use an index.

Was this lesson helpful?

Found an error? Report an issue