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.




Home
The home page
SQL :: Alter Index
Creates and drops indexes and unique keys.
SQL :: Between
Matches values that fall within a given range.
SQL :: Create Index
Creates an index or a unique key to speed up queries.
SQL :: Declare
Declares a variable for the rest of a script.
SQL :: Delete
Delete removes the documents that match the given criteria.
SQL :: Expressions
Literals, operators, comments and variables in KBSQL.
SQL :: Insert
Insert creates new documents in a schema.
SQL :: IsLike
Returns true (1) if the text matches the pattern. The pattern uses the same wildcards as [[Like]]: % matches any number of characters and _ matches a single character.
SQL :: IsNotLike
Returns true (1) if the text does not match the pattern. See [[Like]] for the wildcards.