Offensive Security Researcher & Quantum Cryptography Analyst

How to Use WHERE Clause in PostgreSQL

Abdul Wahab Junaid
How to Use WHERE Clause in PostgreSQL

The WHERE clause is the single most-used piece of filtering logic in SQL — it’s what turns “give me everything” into “give me exactly what I need.” Whether you’re reading data with SELECT, or filtering what gets touched by UPDATE and DELETE, WHERE is doing the same essential job: deciding which rows a statement applies to. This guide covers everything from basic comparisons to more advanced pattern matching, working with nulls, and combining conditions correctly.

Basic Syntax

SELECT * FROM table_name
WHERE condition;

A simple example:

SELECT * FROM customers
WHERE country = 'Pakistan';

This returns only the rows where the country column equals 'Pakistan'. The same WHERE syntax works identically across SELECT, UPDATE, and DELETE.

Comparison Operators

PostgreSQL supports the full standard set:

SELECT * FROM products WHERE price = 29.99;
SELECT * FROM products WHERE price != 29.99;   -- or <>
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE price < 50;
SELECT * FROM products WHERE price >= 50;
SELECT * FROM products WHERE price <= 50;

Combining Conditions with AND, OR, and NOT

SELECT * FROM orders
WHERE status = 'pending' AND total_amount > 100;

SELECT * FROM customers
WHERE country = 'Pakistan' OR country = 'India';

SELECT * FROM orders
WHERE NOT status = 'cancelled';

When mixing AND and OR, parentheses matter — AND binds tighter than OR by default, which can lead to unexpected results if you’re not explicit:

-- Ambiguous intent without parentheses:
SELECT * FROM orders
WHERE status = 'pending' AND total_amount > 100 OR status = 'shipped';

-- Clearer, explicit grouping:
SELECT * FROM orders
WHERE (status = 'pending' AND total_amount > 100) OR status = 'shipped';

Always use parentheses when combining AND and OR in the same condition — it removes any ambiguity about what you actually meant, both for PostgreSQL and for whoever reads the query later.

Filtering with IN

Instead of chaining multiple OR conditions on the same column, IN is cleaner:

SELECT * FROM customers
WHERE country IN ('Pakistan', 'India', 'Bangladesh');

This is functionally identical to:

SELECT * FROM customers
WHERE country = 'Pakistan' OR country = 'India' OR country = 'Bangladesh';

NOT IN works the same way in reverse:

SELECT * FROM orders
WHERE status NOT IN ('cancelled', 'refunded');

A quick word of caution with NOT IN: if the list comes from a subquery that could contain a NULL value, NOT IN can produce surprising empty results, because comparing anything to NULL returns unknown rather than true or false. In that situation, NOT EXISTS is usually the safer choice.

Filtering with BETWEEN

SELECT * FROM orders
WHERE total_amount BETWEEN 50 AND 200;

This is inclusive on both ends — equivalent to total_amount >= 50 AND total_amount <= 200. It works for dates too:

SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

One subtlety with dates: BETWEEN '2026-01-01' AND '2026-01-31' on a TIMESTAMPTZ column only captures midnight of the 31st, not the entire day. If you need the full day included, either use < with the next day, or be explicit with a time component:

SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

This second form is generally the safer pattern for date ranges involving timestamps.

Pattern Matching with LIKE and ILIKE

SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

% matches any sequence of characters (including none), and _ matches exactly one character:

SELECT * FROM products WHERE sku LIKE 'A_123';

LIKE is case-sensitive. For case-insensitive matching, PostgreSQL provides ILIKE:

SELECT * FROM customers
WHERE name ILIKE 'sarah%';

This matches “Sarah”, “sarah”, “SARAH Johnson”, and so on.

For more complex pattern matching, PostgreSQL also supports POSIX-style regular expressions through the ~ (case-sensitive) and ~* (case-insensitive) operators:

SELECT * FROM customers
WHERE email ~* '^[a-z]+@(gmail|yahoo)\.com$';

Handling NULL Values

This is one of the most common sources of confusion for people newer to SQL. NULL represents “unknown” or “absent,” and it doesn’t behave like a normal value in comparisons.

This will not work as most people expect:

SELECT * FROM customers WHERE phone = NULL;  -- Always returns zero rows

Instead, use IS NULL or IS NOT NULL:

SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

Any comparison involving NULL using standard operators (=, !=, <, etc.) evaluates to “unknown” rather than true or false, which is why those rows get silently excluded from your results rather than raising an error — a detail that trips up a lot of people debugging why a query seems to be missing data.

Filtering with EXISTS

EXISTS checks whether a subquery returns any rows at all, without caring about their actual content — often more efficient than IN for larger subqueries:

SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

This returns every customer who has placed at least one order.

SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

This returns customers who have never placed an order — and unlike NOT IN, this handles NULL values in the subquery safely.

Filtering with Arrays

For array-typed columns, PostgreSQL provides operators to check membership:

SELECT * FROM products
WHERE 'electronics' = ANY(tags);

Or using the containment operator:

SELECT * FROM products
WHERE tags @> ARRAY['electronics'];

Filtering JSON/JSONB Data

For JSONB columns, you can filter based on specific keys:

SELECT * FROM events
WHERE payload->>'event_type' = 'signup';

The ->> operator extracts a JSON value as text. For more complex containment checks:

SELECT * FROM events
WHERE payload @> '{"source": "referral"}';

Using Functions and Expressions in WHERE

WHERE isn’t limited to comparing raw columns — you can use functions and calculated expressions too:

SELECT * FROM customers
WHERE LOWER(email) = 'sarah@example.com';

SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2026;

SELECT * FROM products
WHERE price * 1.1 > 100;

Combining WHERE with JOIN

SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'Pakistan' AND orders.status = 'pending';

Filtering conditions apply after the join happens, filtering the combined result set.

Practical Examples

Finding active customers who haven’t ordered recently

SELECT * FROM customers c
WHERE c.is_active = true
  AND NOT EXISTS (
      SELECT 1 FROM orders o
      WHERE o.customer_id = c.id AND o.created_at > NOW() - INTERVAL '90 days'
  );

Searching products by partial name match

SELECT * FROM products
WHERE name ILIKE '%wireless%';

Filtering orders within a price range and specific statuses

SELECT * FROM orders
WHERE total_amount BETWEEN 100 AND 500
  AND status IN ('shipped', 'delivered');

Finding records with missing required data

SELECT * FROM customers
WHERE phone IS NULL OR email IS NULL;

Common Use Cases

Application filtering. Search bars, filter dropdowns, and category browsing in nearly any application translate directly into WHERE conditions.

Data cleanup and validation. Finding incomplete records, duplicates, or data that violates business rules typically starts with a targeted WHERE clause.

Reporting date ranges. Nearly every report — daily, weekly, monthly — filters on a date or timestamp column using WHERE with comparison operators.

Safety checks before destructive operations. As covered in the update and delete guides, testing a WHERE clause with SELECT first is standard practice before running UPDATE or DELETE.

Troubleshooting Common Errors

Query returns no rows unexpectedly. Check for NULL handling issues first — comparisons against NULL with = silently exclude rows rather than erroring. Also double-check case sensitivity if using LIKE instead of ILIKE, and verify date ranges account for time components.

ERROR: operator does not exist. Usually a type mismatch — comparing a text column to a number without proper casting, for example. Use ::type to cast explicitly if needed.

Query is slower than expected. A WHERE clause on an unindexed column in a large table forces a full table scan. Check with EXPLAIN ANALYZE and consider adding an index on frequently filtered columns.

Unexpected results when mixing AND/OR. Almost always an operator precedence issue — add explicit parentheses to group conditions the way you actually intend.

Best Practices

Filtering with Subqueries in WHERE

Beyond IN and EXISTS, WHERE can compare directly against a scalar subquery (one that returns exactly one row and one column):

SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

This returns every product priced above the average across the entire table — a calculation that would be awkward to express without a subquery, since it depends on an aggregate over the whole dataset rather than anything in the current row.

Using ANY and ALL with Subqueries

ANY and ALL let you compare a value against every result of a subquery using standard comparison operators, not just equality:

SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'budget');

This returns products priced higher than every single product in the “budget” category. ANY works similarly but only requires the condition to hold against at least one row in the subquery result:

SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'budget');

Row Value Comparisons

PostgreSQL supports comparing multiple columns at once against a tuple of values, which can simplify certain conditions:

SELECT * FROM orders
WHERE (customer_id, status) = (42, 'pending');

This is equivalent to WHERE customer_id = 42 AND status = 'pending', but the row-value syntax can be more concise, especially when comparing against the result of a subquery that returns multiple columns:

SELECT * FROM orders
WHERE (customer_id, status) IN (
    SELECT customer_id, 'pending' FROM vip_customers
);

Filtering with Multiple Conditions on the Same Column

A subtlety worth knowing: you cannot express “AND” logic across the same array element check with a single = ANY(), and similarly, combining multiple LIKE patterns for an “or any of these” search often reads more clearly with IN or a regular expression than a long chain of OR:

-- Harder to read:
SELECT * FROM products
WHERE name LIKE '%phone%' OR name LIKE '%tablet%' OR name LIKE '%laptop%';

-- Often cleaner with a regular expression:
SELECT * FROM products
WHERE name ~* 'phone|tablet|laptop';

Both return equivalent results here, but for longer lists of alternatives, the regular expression form tends to stay more readable.

Filtering on Computed Boolean Expressions

WHERE accepts any expression that evaluates to a boolean, including more elaborate combinations:

SELECT * FROM orders
WHERE (status = 'pending' AND created_at < NOW() - INTERVAL '7 days')
   OR (status = 'failed' AND retry_count < 3);

This kind of layered condition is common in operational queries — here, finding orders that either have been pending too long, or have failed but still have retries available.

Common Pitfalls with String Comparisons

Text comparisons in PostgreSQL are sensitive to trailing whitespace and case by default:

SELECT * FROM customers WHERE name = 'Sarah ';  -- trailing space, won't match 'Sarah'

If you suspect whitespace issues are causing unexpected mismatches, TRIM() can help diagnose (and sometimes should be applied when cleaning source data, rather than papering over it in every query):

SELECT * FROM customers WHERE TRIM(name) = 'Sarah';

Relying on TRIM() in every query is a workaround, not a fix — if this is a recurring issue, it’s usually better addressed by cleaning the underlying data or adding validation at insert time.

Performance: How WHERE Interacts with Indexes

An index only helps a WHERE clause when the condition can actually make use of it. A few patterns worth knowing:

Checking EXPLAIN ANALYZE on any WHERE clause you’re unsure about is the reliable way to confirm whether an index is actually being used, rather than guessing.

Frequently Asked Questions

Why does WHERE column != 'value' sometimes behave unexpectedly with NULLs? Because rows where column is NULL don’t match != any more than they match = — both comparisons against NULL evaluate to unknown, so those rows are silently excluded either way. If you want to include NULL rows in a “not equal to” filter, you need to add it explicitly: WHERE column != 'value' OR column IS NULL.

Can I use column aliases (defined with AS in the SELECT list) inside the WHERE clause? No — WHERE is evaluated before the SELECT list’s aliases exist, in terms of SQL’s logical processing order. If you need to filter on a computed value, either repeat the full expression in WHERE, or use a subquery/CTE to compute it first and then filter in an outer query.

What’s the actual order SQL processes clauses in? Roughly: FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then the SELECT list itself, then ORDER BY, then LIMIT/OFFSET. This is why WHERE can’t reference SELECT aliases (they don’t exist yet at that stage) but ORDER BY can (since it runs after the SELECT list is resolved).

Is there a performance difference between WHERE ... IN (...) and multiple OR conditions? For a straightforward list of literal values, PostgreSQL’s planner typically optimizes both to equivalent execution plans, so there’s usually no meaningful performance difference. The real difference is readability — IN is generally clearer once you’re checking against more than two or three values.

Wrapping Up

The WHERE clause is small in syntax but massive in importance — nearly every meaningful interaction with a PostgreSQL database, from a simple lookup to a careful, scoped deletion, runs through it. Understanding how comparisons, NULL handling, logical operators, and index usage actually behave (rather than how they seem like they should behave) will save you from some of the most common and frustrating debugging and performance sessions in SQL. It’s worth the time to get genuinely comfortable with it, since it shows up in essentially every query you’ll ever write.

Exit mobile version