Ms Access Outer Join SQL Function

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:

 

Ms Access Outer Join Illustration

Ms Access Outer Join Illustration

 

#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

= 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]

Read Also:  MS Access Inner Join on Multiple Fields and Conditions

Example:

Ms Access Outer Join-1

Ms Access Outer Join (1)

 

Ms Access Outer Join-2

Ms Access Outer Join (2)

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:

Ms Access Outer Join (3)

Ms Access Outer Join (3)

 

• RIGHT JOIN
SELECT *
FROM Sales RIGHT JOIN Pricelist
ON Sales.Product = Pricelist.ProductName;
Result:

Ms Access Outer Join (4)

Ms Access Outer Join (4)

 

• 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:

Ms Access Outer Join (5)

Ms Access Outer Join (5)



Access Database Tags: #access inner join example #access join types #inner join in access #joins in ms access #ms access join expression not supported #ms access left join multiple tables #ms access outer join #ms access outer join multiple tables #ms access outer join query #ms access outer join sql #ms access outer join syntax #outer join access 2013

Ms Access Subquery in Select Statement Syntax
Ms Access Subquery in Select Statement Syntax
Ms Access Subquery syntax is the Select
MS Access Tab Control in Form Tables or Subform
MS Access Tab Control in Form Tables or Subform
Access Tab Control is mainly used in
MS Access Left Function in SQL Query and VBA Code
MS Access Left Function in SQL Query and VBA Code
Ms Access Left Join function or sometimes


Must read×

Top