Left Outer Join

Table of Contents



An outer join (a left outer join) returns every document from the schemas before the join, along with the matching documents of the joined schema. When there is no match, the fields of the joined schema are NULL. This is useful for finding unmatched documents and for optional related data.

Syntax

The keyword is OUTER JOIN. The LEFT keyword is not accepted: LEFT OUTER JOIN fails to parse.


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 every word with the name of its language, including words with no matching language.
SELECT
    W.Text,
    L.Name as Language
FROM
    WordList:Word as W
OUTER JOIN WordList:Language as L
    ON L.Id = W.LanguageId

round-pushpin Selects only the words that have no matching language.
SELECT
    W.Text
FROM
    WordList:Word as W
OUTER JOIN WordList:Language as L
    ON L.Id = W.LanguageId
WHERE
    IsNull(L.Name) = 1




SQL :: Between
Matches values that fall within a given range.
SQL :: Inner Join
Combines documents from two or more schemas based on matching fields.
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 :: Select
Select statements are used to read (or query) data from the database.