Full Outer Join on Access

Question

I need to perform a FULL OUTER JOIN in Access, so I found a thread on Stack Overflow that gave me the following model:

SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID AND AA.Currency = BB.Currency
UNION
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID AND AA.Curency = BB.Currency

So at first it seems to work (around 95% of the results match) but when I check the total sum of a column, it has slightly changed (In my case I have 800K rows, therefore the change is quite important).

Also, some of the IDs in each table are NULL, and each row with an ID NULL needs to be on a different row after the full outer join . I think that some rows, do you know what's wrong ?

Thanks in advance

Possible duplicate of [How do I write a full outer join query in access](https://stackoverflow.com/questions/19615177/how-do-i-write-a-full-outer-join-query-in-access) — forpas, Aug 07 '19 at 09:39
please read the tags description when using them: sql: "Questions should include [...] sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used", access: "DO NOT USE this tag for Microsoft Access, use [ms-access] instead" — Kaddath, Aug 07 '19 at 09:41
forpas, I mentioned that I found the code on another thread from Stack Overflow, but it doesn't work. That's why I created my own thread. — MichMich, Aug 07 '19 at 09:44
Note that in SQL, comparing `{anything} = NULL` will always be `NULL` (false). That's probably why you don't get proper results if you have `ID`s at `NULL`. But I can't really answer you without sample data, what you currently get with it and what you expect instead. — Kaddath, Aug 07 '19 at 09:54
[How to ask a good SQL question](https://meta.stackoverflow.com/a/271056/3820271). Sample data and expected result is much more useful than a textual description. — Andre, Aug 07 '19 at 10:03
@MichMich if it doesn't work then this is not your problem so you should explain better the issue and post sample data and expected results. The title of your question and the explanation you provided is a duplicate. — forpas, Aug 07 '19 at 10:53

score 0 · Answer 1 · answered Aug 07 '19 at 10:50

I would recommend creating a list of all ids/currency combinations and then using left joins:

SELECT AA.*, BB.*
FROM ((SELECT AA.C_ID, AA.Currency FROM AA
       UNION  -- on purpose to remove duplicates
       SELECT BB.C_ID, BB.Currency FROM BB
      ) as IDS LEFT JOIN
      AA
      ON AA.C_ID = IDS.C_ID AND
         AA.Currency = IDS.Currency
     ) LEFT JOIN
     BB
     ON AA.C_ID = BB.C_ID AND
        AA.Currency = BB.Currency;

NULL values pose an issue for the full joins. As with the definition of FULL JOIN in SQL, NULL values do not match. You can extend the logic to also compare NULL values:

SELECT AA.*, BB.*
FROM ((SELECT AA.C_ID, AA.Currency FROM AA
       UNION  -- on purpose to remove duplicates
       SELECT BB.C_ID, BB.Currency FROM BB
      ) as IDS LEFT JOIN
      AA
      ON (AA.C_ID = IDS.C_ID OR AA.C_ID IS NULL AND IDS.C_ID IS NULL) AND
         (AA.Currency = IDS.Currency OR AA.CURRENCY IS NULL AND IDS.CURRENCY IS NULL)
     ) LEFT JOIN
     BB
      ON (BB.C_ID = IDS.C_ID OR BB.C_ID IS NULL AND IDS.C_ID IS NULL) AND
         (BB.Currency = IDS.Currency OR BB.CURRENCY IS NULL AND IDS.CURRENCY IS NULL);

Full Outer Join on Access

1 Answers1