Access SQL Full Outer Join

Question

Hi I was trying to Full Outer join two tables on access because I want to keep all items.

here is my code:

    SELECT aa.*, bb.firstname, bb.lastname, bb.totalcost
    FROM (select IT.*,HR.*from IT
    left join HR on HR.firstname=it.firstname and HR.lastname=IT.lastname)  
    AS 
    aa FULL OUTER JOIN 2016totalcost AS bb ON (bb.lastname=aa.IT.lastname) 
    AND (bb.firstname=aa.IT.firstname);

But I got error syntax error in from clause

Thanks for help

Answer 1

NOTE: The question was tagged Oracle when I answered.

The Oracle syntax would be:

select IT.*, HR.*, bb.firstname, bb.lastname, bb.totalcost
from IT left join
     HR
     on HR.firstname = it.firstname and HR.lastname = IT.lastname full outer join 
     2016totalcost tc 
     on tc.lastname = it.lastname and tc.firstname = it.firstname;

Answer 2

Do a LEFT JOIN and UNION it to a RIGHT Join:

SELECT  * FROM Table1
              LEFT JOIN  Table2
    ON 
        Table1.joincolumn = Table2.joincolumn
UNION
SELECT * FROM Table1
             RIGHT JOIN Table2
    ON 
        Table1.joincolumn = Table2.joincolumn

Screenshot

Below you can see the result of an SQL statement similar to the one above. Both tables are joined using Table1.joinColumn = Table2.joinColumn

• A and B are only in T1
• C and D are in both tables T1 and T2
• E, F, and G are only in T2

Answer 3

Access does not recognize a Full Outer Join.

Here is an example of how to write the equivalent for MSA.

How do I write a full outer join query in access