Sybase Technical Library - Product Manuals Home
[Search Forms] [Previous Section with Hits] [Next Section with Hits] [Clear Search] Expand Search

ANSI outer joins [Table of Contents] How
null values affect joins

Transact-SQL User's Guide

[-] Chapter 4 Joins: Retrieving Data from Several Tables
[-] Outer joins
[-] Transact-SQL outer joins

Transact-SQL outer joins

Transact-SQL includes syntax for both left and right outer joins. The left outer join, *=, selects all rows from the first table that meet the statement's restrictions. The second table generates values if there is a match on the join condition. Otherwise, the second table generates null values.

For example, the following left outer join lists all authors and finds the publishers (if any) in their city:

select au_fname, au_lname, pub_name 
from authors, publishers 
where authors.city *= publishers.city 

The right outer join, =*, selects all rows from the second table that meet the statement's restrictions. The first table generates values if there is a match on the join condition. Otherwise, the first table generates null values.

You cannot include a Transact-SQL outer join in a having clause.

A table is either an inner or an outer member of an outer join. If the join operator is *=, the second table is the inner table; if the join operator is =*, the first table is the inner table. You can compare a column from the inner table to a constant as well as using it in the outer join. For example, if you want to find out which title has sold more than 4000 copies:

select qty, title from salesdetail, titles
where qty > 4000 
and titles.title_id *= salesdetail.title_id

However, the inner table in an outer join cannot also participate in a regular join clause.

An earlier example used a join to find the names of authors who live in the same city as a publisher returned two names: Abraham Bennet and Cheryl Carson. To include all the authors in the results, regardless of whether a publisher is located in the same city, use an outer join. Here is what the query and the 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 
Dick         Straight         NULL 
Meander      Smith            NULL 
Abraham      Bennet           Algodata Infosystems 
Ann          Dull             NULL 
Burt         Gringlesby       NULL 
Chastity     Locksley         NULL 
Morningstar  Greene           NULL 
Reginald     Blotche-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 rows affected) 

The comparison operator *= distinguishes the outer join from an ordinary join. This "left" outer join tells Adaptive Server to include all the 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 in the pub_name column.

The "right" outer join is specified with the comparison operator =*, which indicates that all the rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

Substituting this operator in the outer join query shown earlier gives this result:

select au_fname, au_lname, pub_name 
from authors, publishers 
where authors.city =* publishers.city 
au_fname     au_lname    pub_name 
---------    ---------   --------------- 
NULL         NULL        New Age Books 
NULL         NULL        Binnet & Hardley 
Cheryl       Carson      Algodata Infosystems 
Abraham      Bennet      Algodata Infosystems 
 
(4 rows affected) 

You can further restrict an outer join by comparing it to a constant. This means that you can zoom in on precisely the values you really want to see and use the outer join to list the rows that did not make the cut. Let us look at the equijoin first and compare it to the outer join. For example, to find out which titles had a sale of more than 500 copies from any store, use this query:

select distinct salesdetail.stor_id, title 
from titles, salesdetail 
where qty > 500 
and salesdetail.title_id = titles.title_id 
stor_id title                                                                             
------- --------------------------------------------
5023    Sushi, Anyone? 
5023    Is Anger the Enemy?
5023    The Gourmet Microwave
5023    But Is It User Friendly? 
5023    Secrets of Silicon Valley 
5023    Straight Talk About Computers 
5023    You Can Combat Computer Stress! 
5023    Silicon Valley Gastronomic Treats 
5023    Emotional Security: A New Algorithm
5023    The Busy Executive's Database Guide  
5023    Fifty Years in Buckingham Palace Kitchens 
5023    Prolonged Data Deprivation: Four Case Studies  
5023    Cooking with Computers: Surreptitious Balance Sheets    
7067    Fifty Years in Buckingham Palace Kitchens 
 
(14 rows affected) 

Also, to show the titles that did not have a sale of more than 500 copies in any store, you can use an outer join query:

select distinct salesdetail.stor_id, title 
from titles, salesdetail 
where qty > 500 
and salesdetail.title_id =* titles.title_id 
stor_id     title                                                                             
-------   -------------------------------------------
NULL    Net Etiquette
NULL    Life Without Fear
5023    Sushi, Anyone?
5023    Is Anger the Enemy?
5023    The Gourmet Microwave 
5023    But Is It User Friendly? 
5023    Secrets of Silicon Valley
5023    Straight Talk About Computers 
NULL    The Psychology of Computer Cooking  
5023    You Can Combat Computer Stress!
5023    Silicon Valley Gastronomic Treats 
5023    Emotional Security: A New Algorithm
5023    The Busy Executive's Database Guide 
5023    Fifty Years in Buckingham Palace Kitchens 
7067    Fifty Years in Buckingham Palace Kitchens 
5023    Prolonged Data Deprivation: Four Case Studies  
5023    Cooking with Computers: Surreptitious Balance Sheets 
NULL    Computer Phobic and Non-Phobic Individuals: 
        Behavior Variations 
NULL    Onions, Leeks, and Garlic: Cooking Secrets of the
        Mediterranean 
 
 (19 rows affected)

You can restrict an inner table with a simple clause. The following example lists the authors who live in the same city as a publisher, but excludes the author Cheryl Carson, who would normally be listed as an author living in a publisher's city:

select au_fname, au_lname, pub_name
from authors, publishers
where authors.city =* publishers.city
and authors.au_lname != "Carson"
au_fname     au_lname    pub_name 
---------    ---------   --------------- 
NULL         NULL        New Age Books 
NULL         NULL        Binnet & Hardley 
Abraham      Bennet      Algodata Infosystems 
 
(3 rows affected)


ANSI outer joins [Table of Contents] How
null values affect joins