PostgreSQL WHERE Clause: IN, NOT IN, LIKE, NULL and BETWEEN

Say you need every order over $100 from a US store’s October 2026 sales. The PostgreSQL WHERE clause keeps only rows where the condition is true:

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

I tested this in psql on PostgreSQL 18.6, Windows 64-bit:

 order_id | status  | amount 
----------+---------+--------
      101 | Shipped | 249.00
      103 | Shipped | 132.00
      105 | Pending | 412.30
(3 rows)

Sample tables for the WHERE examples

Six customers, seven orders. Guest order 106 (NULL customer_id), the NULL coupons and order 105 (10/31/2026, 6:45 PM) each trigger a gotcha:

CREATE TABLE customers (
  customer_id int PRIMARY KEY,
  name        varchar(20),
  state       char(2)
);
INSERT INTO customers VALUES
 (1,'Megan Bowen','TX'), (2,'Alex Wilber','CA'), (3,'Adele Vance','WA'),
 (4,'Lee Gu','TX'), (5,'Lidia Holloway','NY'), (6,'Diego Siciliani','CO');
CREATE TABLE orders (
  order_id    int PRIMARY KEY,
  customer_id int,
  status      varchar(10),
  amount      numeric(10,2),
  ordered_at  timestamp(0),
  coupon      varchar(8)
);
INSERT INTO orders VALUES
 (101,1,'Shipped',249.00,'2026-10-01 09:15',NULL),
 (102,2,'Pending',89.50,'2026-10-03 14:02','FALL10'),
 (103,3,'Shipped',132.00,'2026-10-15 10:20',NULL),
 (104,4,'Canceled',15.75,'2026-10-07 11:05',NULL),
 (105,1,'Pending',412.30,'2026-10-31 18:45','VIP5'),
 (106,NULL,'Shipped',64.99,'2026-10-18 13:45',NULL),
 (107,5,'Shipped',89.50,'2026-11-01 00:00','FALL10');

New to this? Start with creating a table in PostgreSQL.

WHERE with multiple conditions: AND, OR and parentheses

AND binds tighter than OR. Same orders table, two versions of “Shipped or Pending, over $200”:

SELECT order_id, status FROM orders
WHERE status = 'Shipped' OR status = 'Pending'
  AND amount > 200;

SELECT order_id, status FROM orders
WHERE (status = 'Shipped' OR status = 'Pending')
  AND amount > 200;

Output:

 order_id | status  
----------+---------
      101 | Shipped
      103 | Shipped
      105 | Pending
      106 | Shipped
      107 | Shipped
(5 rows)

 order_id | status  
----------+---------
      101 | Shipped
      105 | Pending
(2 rows)

How does this work?

Without parentheses PostgreSQL reads Shipped OR (Pending AND > 200), so cheap shipped orders sneak in.

Important: parenthesize any OR that sits next to an AND. For branching inside SELECT, use a CASE expression.

PostgreSQL WHERE IN with a list of values

IN replaces a chain of ORs, and the docs call it equivalent to = ANY:

SELECT 'IN' AS test, count(*) FROM orders
WHERE status IN ('Pending', 'Canceled')
UNION ALL
SELECT '= ANY', count(*) FROM orders
WHERE status = ANY (ARRAY['Pending', 'Canceled'])
UNION ALL
SELECT 'NOT IN', count(*) FROM orders
WHERE status NOT IN ('Pending', 'Canceled');

Output:

  test  | count 
--------+-------
 IN     |     3
 = ANY  |     3
 NOT IN |     4
(3 rows)

Values with apostrophes need escaped single quotes.

WHERE IN with a subquery

Same orders, filtered by a customers subquery: Texas buyers only.

SELECT order_id, customer_id FROM orders
WHERE customer_id IN (
  SELECT customer_id FROM customers WHERE state = 'TX');

Output:

 order_id | customer_id 
----------+-------------
      101 |           1
      104 |           4
      105 |           1
(3 rows)

The inner SELECT returns IDs 1 and 4; the outer WHERE keeps their orders. A JOIN works too.

Why does WHERE NOT IN return no rows?

I hit this in my demo: Diego never ordered, yet NOT IN finds nobody:

SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

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

Output:

 name 
------
(0 rows)

      name       
-----------------
 Diego Siciliani
(1 row)

How does this work?

Order 106 puts a NULL in the list. 6 <> NULL is unknown, so NOT IN rejects every row. NOT EXISTS doesn’t care.

LIKE vs ILIKE in a WHERE clause

LIKE is case-sensitive; ILIKE, a PostgreSQL extension, isn’t:

SELECT name FROM customers WHERE name LIKE 'a%';

SELECT name FROM customers WHERE name ILIKE 'a%';

Output:

 name 
------
(0 rows)

    name     
-------------
 Alex Wilber
 Adele Vance
(2 rows)

WHERE column IS NULL, not = NULL

Same orders, four with no coupon:

SELECT '= NULL' AS test, count(*) FROM orders
WHERE coupon = NULL
UNION ALL
SELECT 'IS NULL', count(*) FROM orders
WHERE coupon IS NULL
UNION ALL
SELECT 'IS DISTINCT FROM', count(*) FROM orders
WHERE coupon IS DISTINCT FROM 'FALL10';

Output:

       test       | count 
------------------+-------
 = NULL           |     0
 IS NULL          |     4
 IS DISTINCT FROM |     5
(3 rows)

= NULL is never true. IS DISTINCT FROM 'FALL10' returns 5 because it counts NULL coupons; plain <> would return 1.

BETWEEN on a timestamp: both ends are inclusive

On a timestamp, ‘2026-10-31’ means midnight, so order 105 at 6:45 PM falls out. Here both WHERE tests run as columns:

SELECT order_id, ordered_at,
  ordered_at BETWEEN '2026-10-01' AND '2026-10-31'
    AS between_test,
  ordered_at >= '2026-10-01' AND ordered_at < '2026-11-01'
    AS half_open
FROM orders
WHERE amount > 200;

Output:

 order_id |     ordered_at      | between_test | half_open 
----------+---------------------+--------------+-----------
      101 | 2026-10-01 09:15:00 | t            | t
      105 | 2026-10-31 18:45:00 | f            | t
(2 rows)

Use >= start AND < next day; the PostgreSQL date range guide covers months.

Single vs double quotes: column does not exist (42703)

Double quotes mark a column name, not a string:

\set VERBOSITY verbose
SELECT count(*) FROM orders WHERE status = "Shipped";

Output and error:

psql:where-double-quotes-error.sql:2: ERROR:  42703: column "Shipped" does not exist
LINE 1: SELECT count(*) FROM orders WHERE status = "Shipped";
                                                   ^
LOCATION:  errorMissingColumn, parse_relation.c:3859

You need single quotes for strings.

Things to Keep in Mind

• WHERE can’t see SELECT aliases or aggregates; filter groups with HAVING.
• The same WHERE drives UPDATE and DELETE. Test it as a SELECT first.
• lower(col) skips a plain index.

Next step: sort the result with ORDER BY. Also useful:

• Counting filtered rows with GROUP BY
• The MariaDB BETWEEN operator

Frequently Asked Questions