Here’s another example of an outer join. It’s a list of all of the publishers who have published books and the titles of those books, along with the names of other publishers who have not.

select    p.pub_id,    p.pub_name,    t.title
from    publishers  p
       left  outer join  titles t  on t.pub_id  =  p.pub_id

pub_id pub_name                  title
----        ---------------                  -----------------------------
0736   New Moon Books            You Can Combat Computer Stress!
0736   New Moon Books           Is Anger the Enemy?
0736   New Moon Books            Life Without Fear
0736    New Moon Books              Prolonged Data Deprivation: Four Case
                                    Stud
0736    New Moon Books             Emotional Security: A New Algorithm
0877     Binnet & Hardley           Silicon Valley Gastronomic Treats
0877    Binnet & Hardley         The Gourmet Microwave
0877    Binnet & Hardley          The Psychology of Computer Cooking
0877    Binnet & Hardley             Computer Phobic AND Non-Phobic
                                     Individuals
0877     Binnet & Hardley              Onions, Leeks, and Garlic: Cooking
                                       Secrets
0877     Binnet & Hardley             Fifty Years in Buckingham Palace
                                      Kitchens
0877      Binnet & Hardley         Sushi, Anyone?
1389        Algodata Infosystems  The Busy Executive’s Database Guide
1389         Algodata Infosystems  Cooking with Computers: Surreptitious
                                   Balan
1389        Algodata Infosystems  Straight Talk About Computers
1389        Algodata Infosystems  But Is It User Friendly?
1389         Algodata Infosystems  Secrets of Silicon Valley
1389        Algodata Infosystems  Net Etiquette
1622        Five Lakes Publishing (null)
1756       Ramona Publishers      (null)
9901      GGG&G                   (null)
9952       Scootney Books         (null)
9999        Lucerne Publishing    (null)

At the end of the list of joined values, you see the list of five unmatched rows. These are the result of the outer join.

---

Note:  Running outer joins forces one of the tables to be the outer table in the join set. As you’ve seen, forcing a join order can cause serious performance problems. Your options for devising a workaround are difficult if a true outer join is needed. If you don’t need the outer join result, avoid it to get better performance.

---

Self-Joins

The self-join occurs when a table needs to be joined with itself. We’ll look at the most common kind of self-join, the “parent/child” relationship. The following example creates and populates a simple employee table that we’ll use to demonstrate the parent/child self-join:

create  table  employees
(empl_id  int  not  null,
  name  varchar(20)  not  null,
  mgr_id  int  null)
go
insert  employees  (empl_id,  name,  mgr_id)  values  (  1, “mary”,  null)
insert  employees  (empl_id,  name,  mgr_id)  values  (10, “bob”,  20)
insert  employees   (empl_id,  name,  mgr_id)    values  (20, “helen”,  40)
insert  employees  (empl_id,  name,  mgr_id)   values  (30, “sara”,  40)
insert  employees   (empl_id,  name,  mgr_id)   values  (40, “frank”,  1)
go
select  *  from employees
go

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

The data is interconnected using the empl_id and mgr_id columns. Bob works for Helen, Helen and Sarah for Frank, and Frank works for Mary. By using a self-join, we’ll display a list of every employee and his/her manager, like this:

e_id        e_name                    m_name
----------         --------------------        --------------------
1            mary                      (none)
10           bob                       helen
20           helen                     frank
30           sara                      frank
40           frank                     mary

Self-joins aren’t really different from regular joins. The problem comes in keeping straight two separate copies of the same table (with the same column names and data). To perform the self-join, you list the table name in the FROM clause twice:

from  employees  e1  join  employees  e2  on  …

Because you need to differentiate between the two copies of the table, table aliases are required with self-joins. (This is the only time that the server requires aliases. Everywhere else, they’re just a convenience.)

---

Note:  I keep referring to two copies of the table, but that’s only a useful logical reference. You see, if you have trouble understanding a self-join, it’s often helpful to write out the two copies of the table and then join them manually as if you were executing the query. It helps you visualize the proper result set.

The server itself doesn’t actually spawn another copy of the table. Instead, it performs the same outer/inner processing we’ve seen in other joins; the only difference is that both inner and outer are the same table.

---

In the preceding FROM clause, the aliases I chose will work, but they are useless to me in defining the relationship between the tables. When you are resolving parent/child self-joins, it’s critical to choose useful aliases. In this example, the two versions should be thought of as a list of employees and a list of managers, so here is the FROM clause:

from  employees  empl join  employees  mgr  on  …

or in traditional SQL:

from  employees  empl,  employees  mgr

What we need to do now is properly describe the join relationship. An employee is related to a manager if his/her manager ID equals a manager’s employee ID, or in SQL terms

empl.mgr_id  =  mgr.empl_id

So that’s the join clause. Here’s what we have so far:

select     [something]
from   employees   empl
          join   employees  mgr  on  empl.mgr_id  =  mgr.empl_id