Transact-SQL includes syntax for both left and right outer joins. The left outer join, *=, selects from the first table all rows 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.
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.
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 SAP ASE 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.
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)
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)
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)