Full Outer Join

FULL [OUTER] JOIN means that record combinations from both source tables meeting a specified condition should be included in the query result. Besides this, those records from both sources without matches must be also included in the query result.

So, all records from both sources will be included in the query result; these will be joined with each other if the specified condition is met. Query result lines without corresponding records from any source will contain NULL in the fields based on records from this source.

OUTER keyword is optional; it is only used to improve the clarity and readability of the query text.

Example:

// All contractors and banks should be added to the report,
// while those that are both contractors and banks should be output in a separate line.

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

FROM
   Catalog.Contractors AS Contractors

FULL OUTER JOIN
   Catalog.Banks AS Banks

BY
   Contractors.Description = Banks.Description

Query result:

Contractor	Bank
Vendors	NULL
"Zarya" knitwear factory	NULL
Jeans factory	NULL
Buyers	NULL
Clothing fair	NULL
"Budenovski" trade house	NULL
Pavilion 45 in wholesale market	NULL
Bavaria China	NULL
Jeans factory	NULL
Promstroibank	Promstroibank
NULL	Ivest Bank

 

See also:
                  Inner Join
                  Left Outer Join
                  Right Outer Join