How to Write a Full Outer Join Query in Access

MS Access Full Outer Join using 2 fields?

I would suggest a union of two left joins to give the same result as full outer, and finally with a touch of sorting to yield the desired ordering:

select c.* from
(
    select a.id, a.value, a.date, b.fcst, b.outdate
    from a left join b on a.id = b.id and a.date = b.outdate
    union
    select b.id, a.value, a.date, b.fcst, b.outdate
    from b left join a on a.id = b.id and a.date = b.outdate
) c
order by c.id, nz(c.date, c.outdate)

Full Outer Joins in MS Access

Access (unfortunately) does not support OUTER JOIN. What it does is INNER, LEFT and RIGHT JOINS.

• INNER JOIN - Gives you only the information common to two tables.
• RIGHT JOIN - Gives all information that are common to the two table and information that are not matched form the table that is on the RIGHT to the JOIN statement.
• LEFT JOIN - Gives all information that are common to the two table and information that are not matched form the table that is on the LEFT to the JOIN statement.
• OUTER JOIN - is the UNION of RIGHT JOIN and LEFT JOIN.

So as expected, you just have to perform two Queries that perform a JOIN in both directions and then marry them using a UNION. This is a long process, but is the only way !

SELECT * FROM 
(SELECT table1.FieldName1, table2.FieldName2
FROM table1 RIGHT JOIN table2 ON table1.ID = table2.ID 
UNION
SELECT table1.FieldName1, table2.FieldName2
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID)

Can I use FULL OUTER JOIN in access VBA?

It depends which database you are trying to connect to. If you are connecting to a database which supports FULL OUTER JOIN, then you could use FULL OUTER JOIN from VBA.

Since you're using CurrentDb.OpenRecordset, you are querying with the Access / Jet database engine, and FULL OUTER JOIN is not supported.

If you are targeting another RDBMS which does support FULL OUTER JOIN, then you could create a pass-through query with FULL OUTER JOIN and call OpenRecordset on that query.

What is the MS Access SQL equivalent of FULL OUTER JOIN with a.key IS NULL and b.key IS NULL

Use:

select . . . 
from a
where not exists (select 1 from b where b.key = a.key)
union all
select . . .
from b
where not exists (select 1 from a where a.key = b.key);

The . . . is for the columns that you want.

You could use * if you used:

select a.*, b.*
from a left join
     b
     on 1 = 0
where not exists (select 1 from b where b.key = a.key)
union all
select a.*, b.*
from b left join
     a
     on 1 = 0
where not exists (select 1 from a where a.key = b.key);

Access FULL JOIN for 7 tables

Since FULL OUTER JOIN is not available in MS Access and requires a UNION ALL of LEFT JOIN and RIGHT JOIN, consider breaking apart the process into several queries. This avoids very nested subquery derived tables:

Query 1 (first two tables)

SELECT a.ClientID, a.PRICE_A, b.PRICE_B
FROM ClientPriceA a LEFT JOIN ClientPriceB b
  ON a.CLIENTID = b.CLIENTID 
WHERE a.ID IS NOT NULL;

UNION ALL 

SELECT b.ClientID, a.PRICE_A, b.PRICE_B
FROM ClientPriceA a RIGHT JOIN ClientPriceB b
  ON a.CLIENTID = b.CLIENTID 
WHERE b.ID IS NOT NULL;

Query 2 (first two tables query + third table) -- GENERATES POSTED DESIRED OUTPUT

SELECT m.*
FROM 
  (SELECT f.ClientID, f.PRICE_A, f.PRICE_B, c.PRICE_C
   FROM Query1 f
   LEFT JOIN ClientPriceC c ON f.ClientiD = c.ClientID
   WHERE f.ClientID IS NOT NULL

   UNION ALL

   SELECT c.ClientID, f.PRICE_A, f.PRICE_B, c.PRICE_C
   FROM Query1 f
   RIGHT JOIN ClientPriceC c ON f.ClientiD = c.ClientID
   WHERE c.ClientID IS NOT NULL
  )  AS m
ORDER BY m.ClientID;

Then repeat process for all others:

Query 3 (first three tables query + fourth table)

...

Query 4 (first four tables query + fifth table)

...

Query 5 (first five tables query + sixth table)

...

Query 6 (first six tables query + seventh table) --FINAL RESULT

...

NOTE: You may run into an MS Access UNION limit or query too complex and so consider outputting queries into temp tables with make-table calls: SELECT * INTO FROM (<query>).

---

Related Topics