In the joins discussed so far, only matching rows (rows with values in the specified columns that satisfy the join condition) are included in the results. In a sense, these join operations eliminate the information contained in the rows that do not match.
However, it is sometimes desirable to retain the nonmatching information by including nonmatching rows in the results of a join. To do this, you can use an outer join. Transact-SQL provides two outer join operators: *=, which includes all rows from the first-named table, and =*, which includes all rows from the second-named table.
Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities where a publisher is located (in this case, Abraham Bennet and Cheryl Carson).
To include all authors in the results, regardless of whether a publisher is located in the same city, use an outer join. Here's what the query and results of the outer join look like:
SELECT au_fname, au_lname, pub_name FROM authors, publishers WHERE authors.city *= publishers.city
au_fname |
au_lname |
pub_name |
----------- |
-------------- |
-------------------- |
Johnson |
White |
(null) |
Marjorie |
Green |
(null) |
Cheryl |
Carson |
Algodata Infosystems |
Michael |
O'Leary |
(null) |
Dean |
Straight |
(null) |
Meander |
Smith |
(null) |
Abraham |
Bennet |
Algodata Infosystems |
Ann |
Dull |
(null) |
Burt |
Gringlesby |
(null) |
Charlene |
Locksley |
(null) |
Morningstar |
Greene |
(null) |
Reginald |
Blotchet-Halls |
(null) |
Akiko |
Yokomoto |
(null) |
Innes |
del Castillo |
(null) |
Michel |
DeFrance |
(null) |
Dirk |
Stringer |
(null) |
Stearns |
MacFeather |
(null) |
Livia |
Karsen |
(null) |
Sylvia |
Panteley |
(null) |
Sheryl |
Hunter |
(null) |
Heather |
McBadden |
(null) |
Anne |
Ringer |
(null) |
Albert |
Ringer |
(null) |
(23 row(s) affected)
The *= operator distinguishes the outer join from an ordinary (equijoin) join. It tells SQL Server to include all rows in the authors table in the results, whether or not there is a match on the city column in the publishers table. Notice that in the results, there is no matching data for most of the authors listed, so these rows contain null values in the pub_name column.
Note Because bit columns do not permit null values, a value of 0 appears in an outer join when there is no match for a bit column in the inner table.
The other outer join operator, =*, indicates that all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table:
SELECT au_fname, au_lname, pub_name FROM authors, publishers WHERE authors.city =* publishers.city
An outer join can be further restricted by comparing it to a constant. This means that you can zoom in on precisely the value or values you want to see, and use the outer join to list the rows that didn't make the cut. For example, to find out which title has sold more than 50 copies, use this query:
SELECT stor_id, qty, title
FROM sales, titles
WHERE qty > 50
AND sales.title_id = titles.title_id
stor_id |
title |
------- |
-------------------- |
7066 |
Is Anger the Enemy? |
(1 row(s) affected)
To also show the titles that didn't sell more than 50 copies in any one store, use an outer join:
SELECT stor_id, qty, title
FROM sales, titles
WHERE qty > 50
AND sales.title_id =* titles.title_id
stor_id |
title |
|
------- |
----------------------------------------------------------- |
|
(null) |
The Busy Executive's Database Guide |
|
(null) |
Cooking with Computers: Surreptitious Balance Sheets |
|
(null) |
You Can Combat Computer Stress! |
|
(null) |
Straight Talk About Computers |
|
(null) |
Silicon Valley Gastronomic Treats |
|
(null) |
The Gourmet Microwave |
|
(null) |
The Psychology of Computer Cooking |
|
(null) |
But Is It User Friendly? |
|
(null) |
Secrets of Silicon Valley |
|
(null) |
Net Etiquette |
|
(null) |
Computer Phobic and Non-Phobic Individuals: Behavior Variations |
|
7066 |
Is Anger the Enemy? |
|
(null) |
Life Without Fear |
|
(null) |
Prolonged Data Deprivation: Four Case Studies |
|
(null) |
Emotional Security: A New Algorithm |
|
(null) |
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean |
|
(null) |
Fifty Years in Buckingham Palace Kitchens |
|
(null) |
Sushi, Anyone? |
|
(18 row(s) affected) |
||
Transact-SQL does not allow both nested outer joins and inner joins nested within an outer join. A table can, however, participate in an inner join and be the outer table in an outer join, as illustrated in this example:
SELECT royaltyper, au_lname, pub_name
FROM titleauthor, authors, publishers
WHERE titleauthor.au_id = authors.au_id
AND authors.city *= publishers.city
The following shorthand can also be used to express the above example:
titleauthor = authors *= publishers
Generalizing and removing table names, this becomes:
T1 = T2 *= T3
Using this notation, the following illustration shows both legal and illegal combinations of inner and outer joins.
The following example is not valid because publishers cannot be the inner table in an outer join and still participate in an inner join:
SELECT title, au_lname, pub_name
FROM authors, publishers, titles
WHERE authors.city *= publishers.city
AND publishers.pub_id = titles.pub_id
The qualification in the query does not restrict the number of rows returned; instead, it affects which rows contain null values. For rows that do not meet the qualification, NULL appears in the inner table's columns of those rows.