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, this 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, 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. The results show 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 narrow it to precisely the values you want to see and use the outer join to list the rows that do not include the specified values. 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)
You can also use an outer join query to show the titles that did not have a sale of more than 500 copies in any store:
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)
