IMPORTANT: We changed our binary download resolvers. Update your config file now.

WHERE

The WHERE clause filters query results based on specified conditions. It allows you to retrieve only the rows that satisfy the filtering criteria, enabling precise data selection.

Syntax

WHERE <condition> [AND|OR <condition>...]

Elements

condition

An expression that evaluates to true or false. Only rows for which the condition evaluates to true are included in the result.

Supported operators

Comparison operators

The WHERE clause supports various comparison operators for creating conditions:

Operator Description Example

=

Equals - Tests if values are equal

name = 'John' or status = :statusParam

!=

Not equals - Tests if values are not equal

category != 'Electronics'

>

Greater than - Tests if left value is greater

price > 100 or created > '2023-01-01'

>=

Greater than or equal to

price >= 100 or age >= 18

<

Less than - Tests if left value is smaller

price < 50 or created < :dateParam

⇐

Less than or equal to

price ⇐ 50 or count ⇐ 10

Logical operators

Logical operators combine multiple conditions:

Operator Description Example

AND

Requires both conditions to be true

category = 'Books' AND price < 20

OR

Requires at least one condition to be true

status = 'new' OR status = 'sale'

NOT

Negates a condition

NOT price > 100 or NOT (status = 'deleted')

Set membership operators

Check if values are members of sets:

Operator Description Example

IN

Tests if a value matches any in a list

category IN ('Books', 'Magazines', 'Comics')

= ANY

Tests if a value matches any in an array column

'electronics' = ANY(tags) or category = ANY(:categoryList)

Null testing

Operators for checking null values:

Operator Description Example

IS NULL

Tests if a value is NULL (missing)

phoneNumber IS NULL

IS NOT NULL

Tests if a value is not NULL (present)

email IS NOT NULL

Pattern matching

Operators for string pattern matching:

Operator Description Example

LIKE

Pattern matching with wildcards

name LIKE 'Jo%' or code LIKE 'ABC_%'

Text search function

Advanced text searching capabilities:

Function Description Example

text_search()

Language-aware text search with word tokenization

text_search(description, :searchTerms, 'english')

Examples

Basic filtering

Simple equality filter
SELECT * FROM products
WHERE category = 'Electronics'
Numeric comparison
SELECT * FROM products
WHERE price < 100
Date comparison
SELECT * FROM customers
WHERE joinDate > '2023-01-01'

Multiple conditions

Combining conditions with AND
SELECT * FROM products
WHERE category = 'Electronics' AND price < 500 AND inStock = true
Combining conditions with OR
SELECT * FROM customers
WHERE region = 'Europe' OR region = 'Asia'
Using parentheses for complex logic
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Computers')
  AND price < 1000
  AND NOT discontinued = true

Working with NULL values

Finding missing values
SELECT * FROM customers
WHERE phoneNumber IS NULL
Finding present values
SELECT * FROM customers
WHERE address IS NOT NULL AND address.street IS NOT NULL

Using sets and arrays

Testing membership in a fixed list
SELECT * FROM products
WHERE category IN ('Electronics', 'Computers', 'Accessories')
Testing membership in a parameter list
SELECT * FROM products
WHERE category IN (:category1, :category2, :category3)
Testing membership in an array column
SELECT * FROM products
WHERE :searchTag = ANY(tags)
Testing membership with an array parameter
SELECT * FROM customers
WHERE status = ANY(:statusList)

Text searching

Pattern matching with prefix
SELECT * FROM customers
WHERE email LIKE 'john.%'
Pattern matching with suffix
SELECT * FROM customers
WHERE email LIKE '%.com'
Full-text search
SELECT * FROM articles
WHERE text_search(content, :searchQuery, 'english')

Accessing nested fields

Filter based on nested object fields
SELECT * FROM customers
WHERE address.country = 'USA' AND address.state = 'California'
Filter with deeply nested fields
SELECT * FROM orders
WHERE shipping.address.zipCode = '10001'

Notes

  • Conditions in the WHERE clause are applied before any GROUP BY, ORDER BY, or LIMIT operations

  • For complex conditions, use parentheses to explicitly control the order of evaluation

  • Type compatibility is enforced - you cannot directly compare values of different types

  • NULL values require special handling with IS NULL and IS NOT NULL - standard comparison operators don’t work with NULL

  • The LIKE operator requires a non-wildcard prefix or suffix for optimal performance

  • For pagination efficiency, ensure your WHERE conditions match available indexes

Related features