7.8. A UNION Used to Implement a Full Outer Join

In Chapter 4, you read that the outer join adds rows to the result set that would otherwise be dropped from an inner join of both tables due to the join condition. Remember that an inner join (also known as an equi-join, ordinary join or regular join) combines two tables by finding common values on some column(s) common to the two tables. In an outer join, we are saying, "we want all the rows from one table and only the joined rows from the other." In SQL Server , the outer joins are in two classesleft and right, depending on how the query is written. A full outer join means that we want all rows from both tables being joined, and "fill in those rows where a join does not produce a result with nulls." In SQL Server , a UNION can also be used to achieve this full outer join.

Some SQL languages do not directly support the full outer join, but SQL Server directly supports it.

In SQL Server , you can create a full outer join by writing a union of the left outer join and the right outer join, like this:

    SELECT with right outer join
    UNION
    SELECT with left outer join

The order of the left outer join and the right outer join does not matter and can be reversed. To illustrate the workings of the UNION version of the full outer join, let us again use the table called Instructor, created earlier in this chapter:

    iname                teaches
    -------------------- --------------------
    Richard              COSC
    Subhash              MATH
    Tapan                BIOCHEM

If we want to get a listing of all instructors and the names of the departments for which they teach (which will be done by a regular equi-join) plus a listing of the rest of the instructors, regardless of whether they belong to a department, plus a listing of the rest of the departments, regardless of whether they have instructors, we would write the following query to achieve the full outer join effect with a UNION:

    SELECT *
    FROM Department_to_major AS d LEFT JOIN Instructor AS I
    ON d.dcode=i.teaches
        UNION
    SELECT *
    FROM Department_to_major AS d RIGHT JOIN Instructor AS I
    ON d.dcode=i.teaches

This query produces the following output (9 rows):

    Dcode DNAME                iname                teaches
    ----- -------------------- -------------------- --------------------
    NULL  NULL                 Tapan                BIOCHEM
    ACCT  Accounting           NULL                 NULL
    ART   Art                  NULL                 NULL
    CHEM  Chemistry            NULL                 NULL
    COSC  Computer Science     Richard              COSC
    ENGL  English              NULL                 NULL
    MATH  Mathematics          Subhash              MATH
    POLY  Political Science    NULL                 NULL
    UNKN                       NULL                 NULL

    (9 row(s) affected)

First, the LEFT JOIN was done, outer joining the department_to_major table and the Instructor table (so that all the rows of the department_to_major table were added to the result set). Then, a RIGHT JOIN was done, again joining the department_to_major table to the Instructor table (but this time all the rows of the Instructor table were added to the result set). Finally, a UNION of the two results sets was performed, creating the effect of a full outer join (where the rows from both the tables were added back after the join).