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:  How to Create or Solve MS Access Enter Parameter Value

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 VBA Close Workbook and Form
MS Access VBA Close Workbook and Form
In this tutorial, we use Ms Access
MS Access DB and MDB Viewer Software
MS Access DB and MDB Viewer Software
Access DB Viewer is the software to
MS Access Validation Rule Lookup Table Examples
MS Access Validation Rule Lookup Table Examples
The Validation Rule in Microsoft Access is


Must read×

Top