Where

Table of Contents



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'.


round-pushpin Selects documents where the text is 'Cat'.
SELECT * FROM WordList:Word WHERE Text = 'Cat'

round-pushpin Selects documents in a range of Ids.
SELECT * FROM WordList:Word WHERE Id BETWEEN 2 AND 4

round-pushpin Selects documents where the text starts with 'Cat'.
SELECT * FROM WordList:Word WHERE Text LIKE 'Cat%'

round-pushpin Uses a function in a condition.
SELECT * FROM WordList:Word WHERE Length(Text) > 6

round-pushpin Combines conditions.
SELECT
    *
FROM
    WordList:Word
WHERE
    Text LIKE 'Cat%'
    AND (LanguageId = 3 OR LanguageId = 1)


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




SQL :: Update
Update changes fields of existing documents.