Full use of--------------------join------------
Outer Joins. An outer join can be a left outer join, a right outer join, or a full outer join.
When you specify an outer join in the FROM clause, you can specify one of the following groups of keywords:
Left JOIN or left OUTER join.
The result set of a left outer join consists of all the rows of the left table specified in the OUTER clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null values.
Right join or right OUTER join.
A right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table.
Full join or full OUTER join.
A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains the data values for the base table.
An inner JOIN returns rows only if at least one row that belongs to the two tables conforms to the join condition. An inner join eliminates a row that does not match any row in another table. An outer join returns all rows of at least one table or view mentioned in the FROM clause, as long as the rows conform to any WHERE or having search conditions. All rows of the left table referenced by a left-outer join are retrieved, along with all the rows of the right table referenced by the right outer join. All rows of two tables in a full outer join are returned.
Microsoft®sql server™2000 uses the following SQL-92 keywords for outer joins specified in the FROM clause:
Left OUTER join or left JOIN
Right OUTER join or RIGHT Join
Full OUTER join or full join
SQL Server supports the SQL-92 outer JOIN syntax and the use of the *= and =* operators in the WHERE clause to specify the old syntax for outer joins. Because the SQL-92 syntax is not prone to ambiguity, and old-style Transact-SQL outer joins are sometimes ambiguous, it is recommended that SQL-92 syntax be used.
Using a LEFT OUTER join
Suppose you join the authors table and the publishers table on the city column. The results are displayed only for authors residing in the publisher's city (in this case, Abraham Bennet and Cheryl Carson).
To include all authors in the results, regardless of whether the publisher lives in the same city, use the SQL-92 left outer join. The following are queries and results for Transact-SQL left-outward joins:
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
Burt Gringlesby NULL
Sheryl Hunter NULL
Livia Karsen NULL
Charlene Locksley NULL
Stearns Macfeather NULL
Heather McBadden NULL
Michael O ' Leary NULL
Sylvia Panteley NULL
Albert Ringer NULL
Anne Ringer NULL
Meander Smith NULL
Dean Straight NULL
Dirk Stringer NULL
Johnson White NULL
Akiko Yokomoto NULL
Abraham Bennet Algodata Infosystems
Cheryl Carson Algodata Infosystems
(Affected row (s))
Whether or not it matches the city column in the publishers table, the left OUTER JOIN contains all the rows of the authors table in the result. Note: Most of the authors listed in the results do not have matching data, so the pub_name columns for these rows contain null values.
Using the right outer join
Suppose you join the authors table and the publishers table on the city column. The results are displayed only for authors residing in the publisher's city (in this case, Abraham Bennet and Cheryl Carson). SQL-92 the right outer join operator right-hand OUTER join indicates that the result will contain all the rows in the second table, regardless of whether there is a matching data in the first table.
To include all publishers in the results, regardless of whether there are any publishers residing in the city, use the SQL-92 right outer join. The following are queries and results of Transact-SQL right outer joins:
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)
Using predicates, such as comparing joins to constants, can further restrict outer joins. The following example contains the same right outer join, but eliminates the title of a book that sells less than 50 copies:
Use pubs
SELECT s.stor_id, S.qty, T.title
From the sales s right OUTER JOIN titles T
On s.title_id = t.title_id
and S.QT
