The tutorial today will discuss about Ms Access Outer Join. Outer Join is the SQL function to join two tables (or multiple tables), then return a set of query records including what inner join would return. There are 3 types of Outer Join:
#1: LEFT JOIN
LEFT JOIN return the result of first table and the matches one with the second table. Using LEFT JOIN to the Sample Diagram above will return: “A”, “B”, and “C”.
The syntax for LEFT JOIN Function:
SELECT [field_1], [field_n]
FROM [table_1] LEFT JOIN [table_2]
ON [table_1.field_1] compopr [table_2.field_2]
Parameters:
[field] = selected field
[table] = field source
[table_n.field_n] = The names of joined fields. No need to have same name, but must have the same data type and contain same criteria.
Compo-pr: comparison operator: “=”, “<“, “>”, “<=”, “>=”, or “<>”.
#2: RIGHT JOIN
RIGHT JOIN return the result of second table and the matches one with the first table. Using RIGHT JOIN to the Sample Diagram above will return: “C”, “D”, “E”, and “F”.
The syntax for RIGHT JOIN Function:
SELECT [field_1], [field_n]
FROM [table_1] RIGHT JOIN [table_2]
ON [table_1.field_1] compopr [table_2.field_2]
#3: FULL JOIN
FULL JOIN combines and returns all data from two or more tables, regardless of whether there is shared information. If the matching data is missing, the other value will be NULL.
Unlike LEFT and RIGHT JOIN, FULL JOIN is not exist in Ms. Access. However, there is tricks to do FULL JOIN in Access, which you can see in this syntax:
SELECT [field_1], [field_n]
FROM [table_1] LEFT JOIN [table_2]
ON [table_1.field_1] compopr [table_2.field_2]
UNION
SELECT [field_1], [field_n]
FROM [table_1] RIGHT JOIN [table_2]
ON [table_1.field_1] compopr [table_2.field_2]
Example:
There are 2 tables that going to be joined. Price-list table is the Table 2, while Sales is Table 1:
1. Go to Create tab > Queries group > Query Design
2. Close the Show Table pop up
3. In Design tab, switch view into SQL view
4. Input the SQL code, then click Run:
• LEFT JOIN
SELECT *
FROM Sales LEFT JOIN Pricelist
ON Sales.Product = Pricelist.ProductName;
Result:
• RIGHT JOIN
SELECT *
FROM Sales RIGHT JOIN Pricelist
ON Sales.Product = Pricelist.ProductName;
Result:
• FULL JOIN
SELECT *
FROM Sales LEFT JOIN Pricelist
ON Sales.Product = Pricelist.ProductName
UNION
SELECT *
FROM Sales RIGHT JOIN Pricelist
ON Sales.Product = Pricelist.ProductName;
Result:
