The question now is, what goes in the SELECT list? Let’s get started by listing all the columns in the table:

select   *
from   employees  empl
          join  employees  mgr  on  empl.mgr_id  =  mgr.empl_id

empl_id       name        mgr_id        empl_id       name     mgr_id
-----------   ---------   -----------   -----------    ------     -------
10            bob         20            20            helen   40
20            helen      40            40             frank   1
30            sara       40            40             frank   1
40            frank      1             1               mary   (null)

Here you see the fully expanded list of employees and their managers. Note that the employee’s mgr_id matches the manager’s empl_id, as our join condition requires.

The columns we are interested in are the first empl_id column and both names. We’ll need to fully qualify every column with the name of a table. While we’re at it, we should also distinguish between the two name columns with a column alias indicating that the name on the right is a manager’s name:

select  empl.empl_id,  empl.name,  mgr.name  AS “manager”
from   employees   empl
          join  employees  mgr  on  empl.mgr_id  =  mgr.empl_id

empl_id     name                       manager
----------           -----------------            ----------------
10           bob                       helen
20           helen                     frank
30           sara                      frank
40           frank                    mary

We’ve pared down the output to the columns we need, and the column headings distinguish which name is the employee and which is the manager. The only problem is that the list of employees is missing Mary. Why?

The problem is that Mary doesn’t have a manager. This is a classic example of an outer join: we want to report all employees, even those who don’t have managers. Once you say it that way, you know which version of the tables is the outer table: the employees.

I prefer not to have the word “null” appear in the final report, so I’ll add an isnull() function to replace null with none. I’ve also sorted the final report by employee name.

select  empl.empl_id,  empl.name,  isnull(mgr.name, “(none)”)  AS
“manager”
from   employees  empl
           left outer join employees mgr on empl.mgr_id = mgr.empl_id
order  by   empl.name

empl_id       name                    manager
-----------         ----------------            ----------------
10             bob                    helen
40             frank                 mary
20             helen                  frank
1              mary                   (none)
30             sara                   frank

A Full Outer Join

One type of outer join that you haven’t seen is called the full outer join.

The full outer join, or double outer join, displays all values from both sides of the join.

Here’s the query and the result:

select  empl.empl_id,  empl.name,  isnull(mgr.name, “(none)”)  AS
“manager”
from   employees  empl
           full outer join employees mgr on empl.mgr_id = mgr.empl_id
order  by  empl.name

empl_id       name                        manager
-----------           -----------------              -----------------
(null)        (null)                     bob
(null)        (null)                      sara
10             bob                        helen
40             frank                     mary
20             helen                      frank
1              mary                       (none)
30              sara                       frank

The full outer join adds two rows to the result set, one each for Bob and Sara, who are not assigned as managers.

---

Note:  Only ANSI syntax can be used to access the full outer join; you cannot use traditional SQL join syntax.

---

Self-Join Dos and Don’ts

Parent/child self-joins are not that difficult if you take your time in building the query and test the results to make certain that you got the relationship right. Use meaningful aliases; then treat the two copies of the table as if they were actually separate tables.

Self-joins are common to many applications in which members of a list are dependent on other members. Examples of this include the employee/manager model we’ve explored here, as well as others such as the following:

•  Parts and assemblies

•  Parents and children

•  Committee members and chairs

Summary

In this chapter, you looked at the inner workings of the join operation. You learned that the server uses indexes and inner/outer tables to process joins, and that join performance can be affected by a number of factors. Watch out for the server making bad assumptions, but avoid forceplan whenever possible. (You’re probably better off creating additional indexes anyway.)

Outer joins allow you to display all rows in one of the tables, even where no corresponding rows are found in the other table(s) of a join set. Full outer joins display all rows from both sides of the relationship.

Self-joins allow you to relate a table to itself. Parent/child self-joins create result sets based on the internal relationship of a table to itself.