Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join Microsoft� SQL Server? 2000 uses these SQL-92 keywords for outer joins specified in a FROM clause:
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
SQL Server supports both the SQL-92 outer join syntax and a legacy syntax for specifying outer joins based on using the *= and =* operators in the WHERE clause. The SQL-92 syntax is recommended because it is not subject to the ambiguity that sometimes results from the legacy Transact-SQL outer joins.
Using Left Outer Joins
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 in which 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 SQL-92 left outer join. The following is the query and results of the Transact-SQL left outer join:
USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Here is the result set:
au_fname au_lname pub_name -------------------- ------------------------------ ----------------- Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL
The LEFT OUTER JOIN includes 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; therefore, these rows contain null values in the pub_name column.
Using Right Outer Joins
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). The SQL-92 right outer join operator, RIGHT OUTER JOIN, indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.
To include all publishers in the results, regardless of whether a city has a publisher located in the same city, use an SQL-92 right outer join. Here is the Transact-SQL query and results of the right outer join:
USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Here is the result set:
au_fname au_lname pub_name -------------------- ------------------------ -------------------- Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (9 row(s) affected)
An outer join can be further restricted by using a predicate (such as comparing the join to a constant). This example contains the same right outer join, but eliminates all titles that have sold fewer than 50 copies:
USE pubs SELECT s.stor_id, s.qty, t.title FROM sales s RIGHT OUTER JOIN titles t ON s.title_id = t.title_id AND s.qty > 50 ORDER BY s.stor_id ASC
Here is the result set:
stor_id qty title
------- ------ ---------------------------------------------------------
(null) (null) But Is It User Friendly?
(null) (null) Computer Phobic AND Non-Phobic Individuals: Behavior
Variations
(null) (null) Cooking with Computers: Surreptitious Balance Sheets
(18 row(s) affected)
For more information about predicates, see WHERE.
Using Full Outer Joins
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft� SQL Server? 2000 provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value.
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 in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables.
To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. The following is the query and results of the Transact-SQL full outer join:
USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Here is the result set:
au_fname au_lname pub_name -------------------- ---------------------------- -------------------- Reginald Blotchet-Halls NULL Michel DeFrance NULL Innes del Castillo NULL Ann Dull NULL Marjorie Green NULL Morningstar Greene NULL Burt Gringlesby NULL Sheryl Hunter NULL Livia Karsen NULL Charlene Locksley NULL Stearns MacFeather NULL Heather McBadden NULL Abraham Bennet Algodata Infosystems Cheryl Carson Algodata Infosystems NULL NULL Binnet & Hardley NULL NULL Five Lakes Publishing NULL NULL GGG&G NULL NULL Lucerne Publishing NULL NULL New Moon Books NULL NULL Ramona Publishers NULL NULL Scootney Books (30 row(s) affected)