Query Join Basics

Recently, I have noticed a few developers, new to Access, showing up in the forums and were confused by the different JOINs that can be used in a query when retrieving basic information from their tables. This blog is my attempt of clarifying some of the basics regarding the different choices when joining or linking tables together in a query.

As I previously mentioned in my blog on Cartesian Query, there are basically three types of joins we can use when using multiple tables in a query:

1. INNER JOIN - returns all matching records between the joined tables
2. LEFT OUTER JOIN - returns all records from the "left-side" table and only the matching records from the "right-side" table (opposite of "right outer join")
3. RIGHT OUTER JOIN - returns all records from the "right-side" table and only the matching records from the "left-side" table (opposite of "left outer join")

INNER JOIN

When two tables are linked using an INNER JOIN in a query, they might look like this:



In this example, the line between Field1 in Table1 and Field1 in Table2 will instruct Access to return all records from Table1 and Table2 where the values in Field1 between the two tables are a match. If Table1 had 15 records in it, and Table2 had 20 records in it but only 12 records match between the two, then the query will only show those 12 records that were a match.

LEFT OUTER JOIN

If we had wanted the query to return all the records from Table1 with all the matching records from Table2, we could use a LEFT OUTER JOIN. To create a LEFT OUTER JOIN, we would start with an INNER JOIN and then right-click on the "join line" between the two tables and select "Join Properties."



The following "Join Properties" dialog window will show up:



As you can see, the initial join was set to include only the records that match between the two tables (Join Type #1). To do a LEFT OUTER JOIN, we just need to select Join Type #2 and click the OK button. Once the Join Properties dialog window closes, the query designer should update the relationship line between the two tables with an arrow head pointing to Table2.



At this point, running the query will return all 15 records from Table1 and show 12 of them with matching values from Table2 and show Null values for the three records with no matching  values from Table2.

RIGHT OUTER JOIN

Creating a RIGHT OUTER JOIN is the same as the above process; but this time, we will select Join Type #3. This join type will return all records from Table2 and display any matching records from Table1. The arrow will now also point to Table1.



Running this query will return all 20 records from Table2 and show 12 of them with matching values from Table1 and show Null values for the eight records with no matching values from Table1.
Please note that if you view the SQL statement of the query when using either LEFT or RIGHT outer joins, the SQL statement does not actually use the keyword "OUTER." Instead, you will just see either "LEFT JOIN" or "RIGHT JOIN."

I hope that helps explain the basics between the different join types in an Access query. They should not be confused with the table joins used in the Relationship Window because, although they may look similar, that is actually somewhat a separate concept.

One final note regarding Access Query Joins, Jet SQL (the SQL language used by Access) does not support a FULL OUTER JOIN that full RDBMS like SQL Server does. To return all the records from both tables in Access, matching or not, you may have to use a UNION QUERY.

Original post date: February 28, 2014