Where

Table of Contents

Overview

Examples

Not supported
See also
Related

The WHERE clause filters the documents that are selected, updated or deleted. Conditions are combined with AND and OR, and can be grouped with parentheses.

Inconsistent schema fields

Fields are not guaranteed to exist in every document. A document that does not have a field used in the WHERE clause does not match it. Every candidate document still has to be read to find out, so prefer conditions on fields that are indexed and present in every document.

Indexes

Conditions that compare an indexed field to a value with = use the index instead of reading every document. A composite index is used when its leading fields are compared with =, and the indexes of several conditions are intersected. See Create Index.

Case sensitivity

=, != and LIKE compare strings without regard to case: WHERE Text = 'cat' matches 'Cat'.

Examples

Selects documents where the text is 'Cat'.

SELECT * FROM WordList:Word WHERE Text = 'Cat'

Selects documents in a range of Ids.

SELECT * FROM WordList:Word WHERE Id BETWEEN 2 AND 4

Selects documents where the text starts with 'Cat'.

SELECT * FROM WordList:Word WHERE Text LIKE 'Cat%'

Uses a function in a condition.

SELECT * FROM WordList:Word WHERE Length(Text) > 6

Combines conditions.

SELECT
    *
FROM
    WordList:Word
WHERE
    Text LIKE 'Cat%'
    AND (LanguageId = 3 OR LanguageId = 1)

Not supported

IN (...) lists. Use OR instead: (Id = 1 OR Id = 2).
The <> operator. Use != instead.
Greater/less than comparisons of strings.