Inner Join

Table of Contents



An inner join combines documents from two or more schemas based on a common field, or a set of common fields. The results contain only the combinations where the join conditions match. Documents without a match are excluded.

Join rules

  • Every joined schema must be given an alias with as.
  • Join conditions compare fields of the joined schema with fields of the schemas before it. Several conditions can be combined with AND and OR.
  • In a query with joins, qualify field names with their schema alias (w.Text, not Text).

Known issue

A join condition that compares a field to a constant (ON tw.Id = s.TargetWordId AND tw.LanguageId = 2) fails in this release with "The given key '' was not present in the dictionary". Put conditions on constants in the WHERE clause instead.


round-pushpin Selects each word and the name of its language.
SELECT
    W.Text,
    L.Name as Language
FROM
    WordList:Word as W
INNER JOIN WordList:Language as L
    ON L.Id = W.LanguageId

round-pushpin Joins the same schema twice to list the translations of a word.
SELECT
    sw.Text as Source,
    tw.Text as Target,
    tl.Name as Language
FROM
    WordList:Word as sw
INNER JOIN WordList:Synonym as s
    ON s.SourceWordId = sw.Id
INNER JOIN WordList:Word as tw
    ON tw.Id = s.TargetWordId
INNER JOIN WordList:Language as tl
    ON tl.Id = tw.LanguageId
WHERE
    sw.Text = 'Dog'




SQL :: Between
Matches values that fall within a given range.
SQL :: IsNull
Returns true (1) if the given value is null, otherwise false (0). This is how to test for missing fields and for the unmatched side of an outer join.
SQL :: Keywords
A breakdown of all of the statements and keywords supported by the query processor.
SQL :: Left Outer Join
Combines documents from two or more schemas, keeping documents that have no match.
SQL :: Select
Select statements are used to read (or query) data from the database.