Full Outer Join in MS Access

Posted: May 18, 2013 by MaxFraudy in MS Access, SQL
Tags: ,

Problem Statement: MS Access does not allow a full outer join query. What this means is that if I have two tables and I want all data from both of these tables, I cannot do it using one join.

Sql full outer join image

Image Credit – http://www.w3resource.com

Let’s take, for example, two tables – Table1 & Table2. Both these tables have employee information but not all employees are common. Table1 has emp_id 9 which is not there in Table2 while Table2 has emp_id 3 which does not appear in Table1. I want all employee IDs, common as well as non-common.


   Table1                           Table2
Emp_id	Salary			Emp_id	Name
1	10000			1	Tom
2	23000			2	Rock
4	40000			3	Thor
9	34000				

Solution: This can be done using a union query, as below:

SELECT T1.EMP_ID 
        FROM 
            TABLE1 as T1 LEFT JOIN TABLE2 as T2 
                    ON T1.Emp_ID = T2.EMP_ID
UNION
SELECT T2.EMP_ID 
        FROM 
            TABLE2 as T2 LEFT JOIN TABLE1 as T1
                    ON T2.Emp_ID = T1.EMP_ID

Result:


EMP_ID
1
2
3
4
9
Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s