Microsoft SQL Server 2008 R2 Unleashed- P174:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1674 CHAPTER 43 Transact-SQL Programming Guidelines Tips and Tricks Panteley White Straight Locksley Carson Blotchet-Halls del Castillo Sylvia 100 Johnson 100 Dean 100 Charlene 100 Cheryl 100 Reginald 100 Innes 100 Note that no real difference exists between left and right outer joins except for specifying which table on which side of the join condition is to be the controlling or outer table. For example the previous query would provide the same result if you reversed the tables in the join clause and made it a right outer join select au_lname au_fname avg royaltyper from ta right outer join a on group by au_lname au_fname order by 3 A full outer join returns all matching rows from both tables along with all rows from each table without a corresponding match in the other table select from a full outer join p on go au_fname pub_name James A. Francine du Plessix Jean . Justin April Ron Jack Matt Josef NULL Kumquat Technical Publishing NULL Nordome Titles Algodata Infosystems Sidney s Books and More Algodata Infosystems NULL NULL NULL . Albert NULL NULL NULL NULL NULL NULL Ramona Publishers Gooseberry Titles Binnet Hardley GGG G Lucerne Publishing Tomato Books Significant Titles Company T-SQL Tips and Tricks 1675 OUTER JOIN Versus WHERE Clause Matching With the ANSI join syntax you specify the join condition in the FROM clause. Additional search conditions can be specified in either the JOIN clause or WHERE clause. It s important to remember that in a left or right outer join search conditions specified in the ON clause work differently than search conditions in the WHERE clause Search conditions in the WHERE clause always exclude nonmatching rows. Search conditions in the ON clause sometimes exclude rows and sometimes do not. Needless to say it can be somewhat confusing to figure out which rows and data values will be returned. To demonstrate the use of .
