Chapter 20: PostgreSQL WHERE – Filter Data

Part 1: What is the WHERE Clause?

The WHERE clause filters rows from a table based on specified conditions. Only rows that satisfy the condition are included in the result set.

The Basic Syntax

The condition can be simple (like price > 100) or incredibly complex with multiple parts combined using logical operators.

A Visual Understanding

Imagine a table as a filing cabinet with many drawers (rows). The WHERE clause is like telling your assistant: “Only show me the files where the customer is from Austin AND they spent more than $50.” Your assistant efficiently scans through, checking each file against your criteria, and returns only the matching ones.

Part 2: Setting Up Our Example Data

Let’s create a rich dataset for a bookstore so we can explore all the WHERE clause features:

Part 3: Basic Comparison Operators

The foundation of WHERE clauses is comparison operators. Let’s explore each one with examples.

1. Equal To (=)

Find books by a specific author:

Result:

2. Not Equal To (!= or <>)

Find all books NOT by Jane Austen:

Note: Both != and <> work the same way. I prefer != for readability.

3. Greater Than (>)

Find books priced above $25:

Result:

4. Less Than (<)

Find books under $10:

Result:

5. Greater Than or Equal To (>=)

Find books with 400+ pages:

Result:

6. Less Than or Equal To (<=)

Find books published in 1950 or earlier:

Result:

Part 4: Logical Operators (AND, OR, NOT)

Real-world queries rarely use just one condition. Logical operators let you combine multiple conditions.

1. AND – Both Conditions Must Be True

Find affordable fiction books in stock:

Result:

Notice that “The Catcher in the Rye” is excluded because it’s out of stock (in_stock = false).

2. OR – At Least One Condition Must Be True

Find books that are either by George Orwell OR published before 1950:

Result:

3. NOT – Negate a Condition

Find books that are NOT in the Fiction genre:

Result (partial):

You can also write this as WHERE genre != 'Fiction'. I find NOT more readable for complex conditions.

4. Combining AND, OR, and NOT with Parentheses

This is crucial! Without parentheses, AND has precedence over OR, which can lead to unexpected results.

Result:

Without parentheses, this happens:

This would include ALL books by Jane Austen regardless of price!

Part 5: Special Operators for More Expressive Conditions

1. BETWEEN – Range Checking

Find books published between 2000 and 2020:

Result:

BETWEEN is inclusive – it includes both boundary values.

Find books with page counts between 300 and 400:

2. IN – Match Any Value in a List

Find books in specific genres:

Result:

This is much cleaner than writing multiple OR conditions:

3. LIKE – Pattern Matching with Wildcards

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

Find books with titles starting with “The”:

Result:

Find books with “of” anywhere in the title:

Result:

Find authors with 5-letter first names (using _ for single characters):

4. ILIKE – Case-Insensitive Pattern Matching

Standard LIKE is case-sensitive. ILIKE ignores case:

5. Regular Expressions with ~ and ~*

For even more powerful pattern matching:

Part 6: NULL Handling

NULL represents missing or unknown data. Working with NULL requires special operators.

1. IS NULL – Find Missing Values

Find books with missing prices:

Result:

2. IS NOT NULL – Find Values That Exist

Find books with all information present:

3. The NULL Trap – Common Mistake

Why? NULL is not a value – it’s the absence of a value. You can’t compare with equals; you must use IS NULL.

4. NULL in Comparisons

When NULL appears in comparisons, the result is always NULL (which is treated as false in WHERE):

NULL values are excluded from all comparison conditions unless explicitly included with IS NULL.

Part 7: Date and Time Filters

Working with dates is incredibly common in real applications.

1. Exact Date Matching

2. Date Ranges

3. Date Functions in WHERE

Find books published in the last 50 years:

Find books published in the 21st century:

Find books published in specific months:

Part 8: Complex Real-World Examples

Example 1: Advanced Book Search

Find highly-rated, moderately-priced fiction books in stock:

Example 2: Publisher Analysis

Find publishers with specific patterns and conditions:

Example 3: Inventory Management

Find books that need reordering (low stock, but popular):

Example 4: Multi-Criteria Customer Search

Imagine we have a customers table (let’s create one quickly):

Part 9: Combining WHERE with Other Clauses

WHERE with ORDER BY

WHERE with GROUP BY and HAVING

The WHERE clause filters rows BEFORE grouping, while HAVING filters groups AFTER grouping:

WHERE with LIMIT

Part 10: Performance Considerations

1. Indexes for WHERE Clauses

Indexes dramatically speed up WHERE conditions. Create indexes on columns frequently used in WHERE:

2. Use EXPLAIN to Understand Query Execution

This shows whether PostgreSQL uses your indexes and how it executes the query.

3. Be Careful with Functions on Indexed Columns

4. LIKE Performance

• LIKE 'pattern%' can use an index (anchored at start)

• LIKE '%pattern' cannot use a standard index (wildcard at start)

• Consider pg_trgm extension for text search with leading wildcards

Part 11: Common Mistakes and How to Avoid Them

Mistake 1: Using = with NULL

Mistake 2: Forgetting Quotes Around Strings

Mistake 3: Mixing AND/OR Without Parentheses

Mistake 4: Using IN with an Empty List

Mistake 5: Not Handling NULL in NOT IN

Mistake 6: Assuming Boolean Comparisons

I recommend using explicit = true and = false for clarity.

Summary: The WHERE Clause Philosophy

The WHERE clause is your primary tool for asking precise questions of your data. Master these concepts in order:

1. Basic comparisons (=, !=, >, <, >=, <=)

2. Logical combinations (AND, OR, NOT with parentheses)

3. Special operators (BETWEEN, IN, LIKE, ILIKE)

4. NULL handling (IS NULL, IS NOT NULL)

5. Date and time filtering

6. Performance optimization (indexes, EXPLAIN)

Remember the execution order:

• FROM (identify table)

• WHERE (filter rows) – happens HERE!

• GROUP BY (group filtered rows)

• HAVING (filter groups)

• SELECT (choose columns)

• ORDER BY (sort results)

• LIMIT (limit output)

Every time you write a WHERE clause, you’re refining your question to the database. Start broad, then narrow down step by step. Test with small result sets first, then add complexity.

The WHERE clause transforms you from someone who just looks at data into someone who can interrogate it, find patterns, answer questions, and make data-driven decisions. It’s the difference between “Here’s our entire customer list” and “Here are our high-value customers in Texas who haven’t ordered in 6 months.”

Practice with these examples, experiment with your own data, and soon filtering with WHERE will become second nature!

Would you like me to elaborate on any specific aspect of the WHERE clause, such as more complex conditions, performance tuning, or integration with joins and subqueries?