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?
05 Answers
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_IDIf 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 NULLHowever, this assumes that AA.C_ID and BB.C_ID are not null.
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 NULLI 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;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"
A full/outer join can be achieved this way in MS Access (or any standard SQL language):
SELECT t1.* , t2.*
FROM (SELECT table01.* , 1 AS [_x1] FROM table01) t1
INNER JOIN
(SELECT table02.* , 1 AS [_x2] FROM table02) t2
ON t1.[_x1] = t2.[_x2];The only things that may be an issue are the two columns of ones from the [_x] columns. That can be addressed if the user specifies the columns they want (replace t1.* and/or t2.* with t1.[ColumnName11], t1.[ColumnName12],..., etc., t2.[ColumnName21], t2.[ColumnName22],..., etc..
