What is a Join: Part 1 (Introduction)

What is a Join: Part 1

Introduction

This is the beginning of a multi-part series devoted to Joins in Microsoft Access.

A Join connects the records of two tables based on common information in the records. In What Is Normalization: Part II, I discussed how to organize data into smaller tables to reduce redundancy. In What is Normalization: Part III, I discussed relationships and their role in defining how tables should be put back together so all of the data is retained. The Join is a SQL (Structured Query Language) implementation of a relationship. In other words, if normalization splits tables apart, the Join is how you put them back together.

In Select Queries: Part I, I talked briefly about the SQL FROM clause, where Joins are created. The general SQL syntax for the most common type of Join (Equi-Join) is:

FROM Table1 JOIN Table2 ON Table1.JoinField = Table2.JoinField

Where JoinField is a field in each table which holds the same data. So, if I had Order and OrderDetail tables, they would both have to have an OrderID field in common to relate the records in the two tables. The FROM clause for this join would be:

FROM Order JOIN OrderDetails ON Order.OrderID = OrderDetails.OrderID

Although the Equi-Join (also known as an Inner Join) is the most common, there are many different types of joins:

1. Equi-Join (Inner Join)
   
2. Cartesian Join (Cross Join)
   
3. Equi-Joins in the Where Clause
   
4. Outer Join (Left Join or Right Join)
   
   1. Unmatched Query
      
   2. Full Outer Join
      
5. Self-Join
   
6. Theta Join
   

In subsequent posts, I'll be discussing each of these in turn. Next up, the Equi-Join.

.