Select

Table of Contents

Overview
Field aliases

Examples

See also
Related

SELECT reads documents from one or more schemas.

SELECT [DISTINCT] [TOP n] { * | alias.* | expression [as Name], ... }
[INTO TargetSchema]
FROM Schema [as alias]
[INNER JOIN | OUTER JOIN Schema as alias ON conditions] ...
[WHERE conditions]
[GROUP BY expression, ...]
[ORDER BY expression [ASC | DESC], ...]
[OFFSET n]

Notes on SELECT *

SELECT * returns every field found in the selected documents. Since documents in a schema don't need to have the same fields, SELECT * must examine every selected document to build the list of fields.

Inconsistent schema fields

Fields are not guaranteed to exist in every document. Fields that are selected but missing from a document are returned as NULL. Set WarnMissingFields to be warned about them.

Field aliases

Fields and expressions can be given a name with as. The as keyword is required. Names that contain spaces must be quoted. Expressions without a name are returned as Expression0, Expression1 and so on.

Examples

Selects all fields from the Word schema.

SELECT * FROM WordList:Word

Selects specific fields, an expression and a function from the Word schema.

SELECT
    Id,
    Text,
    Id * 10 as Score,
    ToUpper(Text) as 'Upper Case'
FROM
    WordList:Word
WHERE
    LanguageId = 1

Selects all of the fields of one of the joined schemas.

SELECT
    w.*
FROM
    WordList:Word as w
INNER JOIN WordList:Language as l
    ON l.Id = w.LanguageId
WHERE
    l.Name = 'German'