30

Original query:

SELECT * 
FROM AA
FULL OUTERJOIN BB on (AA.C_ID = BB.C_ID);

How do I convert the query above to make it compatible in Microsoft Access?

I am assuming:

SELECT *
FROM AA
FULL LEFT JOIN BB ON (AA.C_ID = BB.C_ID);

I haven't dealt with the "FULL" criteria before am I correctly converting the first query into a query compatible with Access?

kjmerf
  • 4,275
  • 3
  • 21
  • 29
user2924488
  • 301
  • 1
  • 3
  • 3

4 Answers4

37

Assuming there are not duplicate rows in AA and BB (i.e. all the same values), a full outer join is the equivalent of the union of a left join and a right join.

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

If there are duplicate rows (and you want to keep them), add WHERE AA.C_ID IS NULL at the end, or some other field that is only null if there is not corresponding record from AA.

EDIT:

See a similar approach here.

It recommends the more verbose, but more performant

SELECT *
    FROM AA
        JOIN BB ON AA.C_ID = BB.C_ID
UNION ALL
SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE BB.C_ID IS NULL
UNION ALL
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE AA.C_ID IS NULL

However, this assumes that AA.C_ID and BB.C_ID are not null.

Paul Draper
  • 78,542
  • 46
  • 206
  • 285
  • Wouldn't it be simpler to just use `UNION ALL` instead of `WHERE` clauses? – Zev Spitz May 11 '18 at 05:54
  • Good Answer+ except the second example doesn't work as-is in Access. I changed the first `JOIN` to `INNER JOIN` and it seems to do what it's supposed to. – ashleedawg Aug 31 '18 at 09:12
13

The more eficient and faster code:

SELECT *
    FROM AA
        LEFT JOIN BB ON AA.C_ID = BB.C_ID
UNION ALL
SELECT *
    FROM AA
        RIGHT JOIN BB ON AA.C_ID = BB.C_ID
    WHERE AA.C_ID IS NULL
Satej S
  • 2,113
  • 1
  • 16
  • 22
user6012447
  • 131
  • 1
  • 2
1

I found that if the field names are the same in both tables they will need to be listed individually rather than using the * operator. Also, the second SELECT statement needs to reference the other table. Simply using the same SQL as the first and changing it to a RIGHT JOIN does not allow the inclusion of the rows in the BB table.

SELECT AA.C_ID
FROM AA
LEFT JOIN BB ON 
  AA.C_ID = BB.C_ID
UNION ALL 
SELECT BB.C_ID
FROM BB
LEFT JOIN AA ON 
  AA.C_ID = BB.C_ID
WHERE AA.C_ID IS NULL;
Daniel Bickler
  • 1,119
  • 13
  • 29
Casey35
  • 11
  • 1
0

Or... You could create a query with unique records on the field that you need:

SELECT DISTINCT AA.C_ID
FROM AA
UNION
SELECT DISTINCT BB.C_ID
FROM BB;

And with that query you can do a left join with both of the tables on "C_ID"

Alext
  • 11
  • 3