You Can Simulate a Full Outer Join by Using
There are many different kinds of joins, similar INNER Join, LEFT OUTER Join, Right OUTER Join, and Total OUTER Join. The kickoff 3 of these unlike join types are implemented in Admission, but Total OUTER JOIN is not. If you lot need to perform a Full OUTER JOIN in Access, are you "up a creek, without a paddle"? No, you are not. This article will prove you how you can write some SQL lawmaking in Access to implement a faux FULL OUTER Bring together.
What is a Total OUTER Join?
A Full OUTER join operation on two record sets returns all of the records independent in both sets fifty-fifty if they do not meet the join criteria. For each record returned that does not take matching records in the corresponding set, a Zip value will be returned for each column selected from the corresponding set up.
When you bring together two tables you tin can think of the records involved falling into three unlike unique record sets. The first record set contains only those records meeting the bring together criteria. This is the set produced when you perform an INNER Bring together. The 2nd gear up of records is the set of records in the left table that do not meet the join criteria, or are said to non have matching records in the right table. And the final set of records is the gear up of records in the right table that practice not have matching records in the left table. The following diagram illustrates the 3 different sets:
In the to a higher place diagram, the two tables are represented by the two circles, labeled Table A, and Table B. The iii different sets are represented past the iii unlike sections created by the intersection of the two different circles, labeled Set i, Set 2 and Set iii. Set ane is the set of records in Table A and B that see the join criteria. Prepare two is the set of records in Table A that do not lucifer any records in Table B. And Gear up 3 is the fix of records in Table B that practise not match whatever records in Table A. A FULL OUTER join would render a record set that contains all the records from these iii unlike sets 1, ii and 3.
How to Perform a Simulated FULL OUTER Bring together in Access
Access does not support the FULL OUTER bring together clause. Therefore, to simulate the Total OUTER join functionality you need perform three different joins, then Matrimony together the three different result sets. The 3 different upshot sets would exist similar to the three different sets discussed in the prior department. Where Set one would be created using an INNER JOIN, Set 2 would exist created with a LEFT OUTER Join and Gear up 3 would be created using a RIGHT OUTER Join. To show you lot how this works lets go though an example using the sample Northwind Access database.
For my example, say I want to perform a FULL OUTER Bring together between the Northwind Employees table and the Suppliers tabular array where I am joining these two tables on the Metropolis column. In my final event ready I want to brandish Employees.LastName, Employees.City, Supplies.CompanyName, and the Suppliers.City for all records in both these tables regardless of whether or not there a matching records.
To simulate a Total OUTER Join query I volition first create three separate queries, one for each of the sets mentioned above. To do that, my Query Blueprint and Bring together Properties view for Gear up i would look like this:
The SQL code backside this pattern view would expect similar this:
SELECT Employees.LastName, Employees.Metropolis, Suppliers.CompanyName, Suppliers.City FROM Employees INNER Bring together Suppliers ON Employees.City = Suppliers.Urban center;
Later running this SQL code, I would only return the records for Set i. Below is the consequence set up for this query:
As you lot can meet, merely 4 Employees records joined to a single Suppliers record. Discover that this first fix was created with an INNER Join clause.
The 2nd query, Ready 2, needs to perform a LEFT OUTER Join between the Employees and Suppliers table. However, I but desire to return those records that practise not accept matching supplier records. Here is the Pattern and Join Properties view of how I constructed this query:
As y'all can see, I have now selected to "Include All records from 'Employees' and only those records from 'Suppliers' where the join fields are equal" for the join holding. Although, since I included a criteria that says only return those records where the Suppliers.City is null, I volition only return the employee records that practise not have a supplier in the same city. Here is the SQL lawmaking backside this query:
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City FROM Employees LEFT Join Suppliers ON Employees.City = Suppliers.Metropolis WHERE (((Suppliers.City) Is Null));
When I run this query, I go the following results:
As you can see, the supplier columns are empty. In that location are but five employees that do not have suppliers in their city.
The last set I need to produce is Ready 3, or the fix that contains all the suppliers that do not have employees in their metropolis. Below is the Design and Join Property view of how to construct this query:
Here you tin come across I pick the third join criteria, "Include ALL records from 'Suppliers' and only those records from 'Employees' where the joined fields are equal." Nevertheless, since I specified that I only wanted supplier records where the Employees.Urban center cavalcade "Is Zero," only suppliers that do non have employees in their city will be returned. Hither is the SQL code behind the screen to a higher place:
SELECT Employees.LastName, Employees.Metropolis, Suppliers.CompanyName, Suppliers.Urban center FROM Employees Correct JOIN Suppliers ON Employees.Urban center = Suppliers.City WHERE Employees.City is nada;
When I run this query, I get the following results:
Here you can see I returned 28 Suppliers that have a null value in the Employees.City Column.
Now that I have all three query developed all I need to practice is put everything together to produce my simulated FULL OUTER JOIN. To practice that I just have my three different SELECT statements for set 1, 2, and three and put them together with a Marriage ALL statement in between. In Access, I practice this by using the "SQL View" of the Query Blueprint view. Hither is the SQL code for my final faux FULL OUTER Bring together:
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.Urban center FROM Employees INNER Bring together Suppliers ON Employees.City = Suppliers.City Wedlock ALL SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City FROM Employees LEFT Join Suppliers ON Employees.City = Suppliers.Metropolis WHERE (((Suppliers.Metropolis) Is Naught)) Union ALL SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City FROM Employees RIGHT JOIN Suppliers ON Employees.Metropolis = Suppliers.Urban center WHERE Employees.Metropolis is null;
When I run this query, I become the post-obit results:
Note that this query returned a total of 37 rows. The first 4 rows are the records returned for the INNER Bring together I called Ready 1. As you lot tin run into both the employee and supplier columns take information in them for this fix. The next five rows are for Set 2, the LEFT OUTER Bring together, and therefore they do not incorporate whatever cavalcade data for the supplier table columns. And the concluding 28 rows are for Set 3 the Correct OUTER Join, then in these records there is no employee data.
Conclusion
Just because Access does not support the Full OUTER JOIN clause does not mean you cannot do a Full OUTER JOIN. It only ways you have to exist a niggling more creative to achieve a Full OUTER join. By thinking of the pieces it takes to build a FULL OUTER, I was able to build my own simulated Total OUTER JOIN in Access. My method would be similar to how you lot could simulate the multiplication role using a series of add functions. Next time you find that Access is missing some ANSI SQL functionality attempt to think of the process that would exist used to produce the functionality. By doing this, you may find you can build your own simulated method of accomplishing the functionality missing in Access.
» See All Manufactures by Columnist Gregory A. Larsen
maldonadofars1954.blogspot.com
Source: https://www.databasejournal.com/ms-access/implementing-the-equivalent-of-a-full-outer-join-in-microsoft-access/
Post a Comment for "You Can Simulate a Full Outer Join by Using"