How can I do a full outer join?

How can I do a full outer join?

I have two queries. The first returns a list of all the documentID and employeeID combos. The second returns a list of documentID and employeeID for what the employee is trained on. I'm using Access, and I was wondering how can I get it to do a full outer join on the two, so that I can get all the combos from the first query that don't exist in the combos of the second query?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

What you're asking for is an exception join, not a full outer join. Here's the preferred way to do an exception join:

select documentID, employeeID
  from q1
except
select documentID, employeeID
  from q2

This returns all combinations from q1 that don't exist in q2. The EXCEPT syntax is basically the same as UNION syntax, in that both of them operate on two subqueries, but rather than combining result sets like UNION does, EXCEPT removes rows from the first result that are also in the second. The EXCEPT syntax is the preferred way because it's so obvious what's going on.

If your database does not support the EXCEPT set operator, you can still do an exception join like this:

select documentID, employeeID
  from q1
 where not exists
       ( select 1 
           from q2
          where documentID = q1.documentID
            and employeeID = q1.employeeID )

This may be a little more verbose, but you can still see reasonably well what's going on, except maybe for SELECT 1. This is sometimes written SELECT *. You can select whatever you wish, because it doesn't matter. The NOT EXISTS subquery isn't returning anything except TRUE or FALSE anyway.

Yet another way to do an exception join is with a left outer join testing for nulls in the right table columns:

select documentID, employeeID
  from q1
left outer join q2
    on q1.documentID = q2.documentID
   and q1.employeeID = q2.employeeID
 where q2.documentID is null
   and q2.employeeID is null

Although it's not immediately obvious that this is an exception join, it works just fine. When a row from the table on the left side of the left outer join doesn't match any row from the right table, the result row will have nulls in all the columns from the right table. The rows with nulls are the only result rows you want.

A full outer join, in case you were curious, would be all the rows of the first table that aren't in the second, plus all the rows of the second table that aren't in the first. The syntax is actually quite simple:

select documentID, employeeID
  from q1
full outer join q2
    on q1.documentID = q2.documentID
   and q1.employeeID = q2.employeeID

Not all databases support the FULL OUTER JOIN syntax, though, but you can always get a full outer join by taking a UNION of two exception joins. However, this wouldn't make sense in your example, because employees who took training would obviously be in the main query, right?


This was first published in August 2002

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top