Join Specifications

When you define multiple sources in the source list, for every record in the first source table, a selection is made from the second source table, etc. Hence, the query result will have all the possible combinations of all the records from all the specified sources.

Example:

SELECT
   Contractors.Link AS Contractor,
   Banks.Link AS Bank

FROM
   Catalog.Contractors AS Contractors,
   Catalog.Banks AS Banks

Query result:

Contractor	Bank
Vendors	AKB InvestBank
Vendors	AKB PromStroyBank
Zarya Knitting Factory	AKB InvestBank
Zarya Knitting Factory	AKB PromStroyBank
Jeans Factory	AKB InvestBank
Jeans Factory	AKB PromStroyBank
Buyers	AKB InvestBank
Buyers	AKB PromStroyBank
Clothing Fare	AKB InvestBank
Clothing Fare	AKB PromStroyBank
Budenovsky Trade House	AKB InvestBank
Budenovsky Trade House	AKB PromStroyBank
45th Booth on Wholesale Market	AKB InvestBank
45th Booth on Wholesale Market	AKB PromStroyBank
Bavaria – Chinaware	AKB InvestBank
Bavaria – Chinaware	AKB PromStroyBank
Jeans Factory	AKB InvestBank
Jeans Factory	AKB PromStroyBank
AKB PromStroyBank	AKB InvestBank
AKB PromStroyBank	AKB PromStroyBank

The query result includes combinations of all the contractors with all the banks. Normally this result does not make sense as such. Usually combinations of records from various source tables should be limited by certain conditions. The query language enables you to describe such a join of sources by specifying the sources and conditions that should be met to include a record combination in the query result.

Joins can belong to multiple types and are described by the following rules:

<Join list>
	|
	<Join> [<Join list>]
		|
		[INNER] JOIN <Source description> BY <Filter condition> | LEFT [EXTERNAL] JOIN <Source description> BY <Filter condition> | RIGHT [EXTERNAL] JOIN <Source description> BY <Filter condition> | FULL [EXTERNAL] JOIN <Source description> BY <Filter condition>

In general, <Join list> can include and describe not only a single join (of two sources) but also multiple joins of several sources.

<Source description> includes the description of a source table.

<Filter condition> contains the conditions that should be met to join data from source tables of the query in the resulting selection.

LEFT, RIGHT and FULL keywords specify the nature of a join. The INNER or EXTERNAL keyword can be omitted but it does improve readability of the query text.

Joined sources are not equal and in some cases the results depend on which table is specified as the first one before the JOIN keyword (to the left of the keyword) and which one goes second (to the right).