Use of the right outer join for connecting left outer joins within Oracle, (+) symbolic usage

1. Internal connection is simple
Select A.*, b.* from, b where a.id = b.ID
Select A.*, b.* from A inner join B on a.id = b.ID
The above two sentences are completely equivalent.

2. Left Outer connection
SELECT * FROM EMP a LEFT JOIN Dept D on A.deptno=d.deptno
SELECT * from emp a,dept D where A.deptno=d.deptno (+)

The above two sentences are completely equivalent.

3. Right outer connection
SELECT * from EMP a right joins dept D on A.deptno=d.deptno
SELECT * from emp a,dept D where A.deptno (+) =d.deptno
The above two sentences are completely equivalent.

In other words, you can do the same thing by changing the table in the statement to a different position, right outside and left.

Oracle can use "(+)" to indicate that 9i can use Left/right/full OUTER JOIN

OUTER JOIN: Left Outer Association
SELECT E.last_name, e.department_id, D.department_name
From Employees E
Left OUTER JOIN Departments D
On (e.department_id = d.department_id);
Equivalent to
SELECT E.last_name, e.department_id, D.department_name
From Employees e, departments D
WHERE e.department_id=d.department_id (+)
The results are: Records of all employees and corresponding departments, including employee records with no corresponding department number department_id.

Right OUTER JOIN: Left Outer Association
SELECT E.last_name, e.department_id, D.department_name
From Employees E
Right OUTER JOIN Departments D
On (e.department_id = d.department_id);
Equivalent to
SELECT E.last_name, e.department_id, D.department_name
From Employees e, departments D
WHERE e.department_id (+) =d.department_id
The results are: Records of all employees and corresponding departments, including departmental records without any employees.

Full OUTER JOIN: All-Out Association
SELECT E.last_name, e.department_id, D.department_name
From Employees E
Full OUTER JOIN Departments D
On (e.department_id = d.department_id);
The result: Records of all employees and corresponding departments, including employee records without corresponding department number department_id and departmental records without any employees.

The complete usage of Sql--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 it by one of the following sets of keywords:

Left JOIN or left OUTER join.
The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list 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 of 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 will be returned for left table.

Full join or full OUTER join.
A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

An inner JOIN returns rows only if at least one row that belongs to two tables meets the join condition. An inner join eliminates rows that do not match any rows in another table. The outer join returns all rows of at least one of the tables or views mentioned in the FROM clause, as long as the rows conform to any WHERE or having search condition. All rows of the left table referenced by the left outer join are retrieved, along with all rows of the right table referenced by the right outer join. All rows from both tables in a full outer join are returned.

microsoft®   SQL server& #8482; 2000 use the following SQL-92 keyword for the outer join 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 specifies the legacy syntax for outer joins using the *= and =* operators in the WHERE clause. Because SQL-92 syntax is not prone to ambiguity, and older Transact-SQL outer joins sometimes create ambiguity, it is recommended that you use the SQL-92 syntax.

Use left outward join
Suppose you join the authors table and the publishers table on the city column. The results show only the authors residing in the city where the publisher resides (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 outward join. The following is a query and result of a 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
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

(s) affected)

The left OUTER JOIN includes all rows of the authors table in the result, regardless of whether it matches the city column in the publishers table. Note: Most of the authors listed in the results do not have matching data, so the pub_name column for these rows contains null values.

Using a right-to-outer join
Suppose you join the authors table and the publishers table on the city column. The results show only the authors residing in the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson). SQL-92 Right-outer join operator OUTER join indicates that, regardless of whether there is a matching data in the first table, the result will contain all rows from the second table.

To include all publishers in the results, regardless of whether or not a publisher resides in the city, use the SQL-92 right-side join. The following is a query and result of a Transact-SQL right-outward 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)

You can further restrict outer joins by using predicates, such as comparing joins to constants. The following example contains the same right outer join, but eliminates the title of a book that sells less than 50 books:

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
(NULL)   (NULL) Emotional security:a New algorithm
(NULL)   (NULL) Fifty years in Buckingham Palace Kitchens
7066 is Anger the Enemy?
(NULL)   (NULL) Life without Fear
(NULL)   (NULL) Net Etiquette
(NULL)   (NULL) onions, leeks, and garlic:cooking secrets of the
Mediterranean
(NULL)   (NULL) Prolonged Data Deprivation:four Case studies
(NULL)   (NULL) Secrets of Silicon Valley
(NULL)   (NULL) Silicon Valley Gastronomic Treats
(NULL)   (NULL) Straight Talk about Computers
(NULL)   (NULL) Sushi, anyone?
(NULL)   (NULL) The Busy Executive ' s Database guide
(NULL)   (NULL) The Gourmet microwave
(NULL)   (NULL) The Psychology of computer Cooking
(NULL)   (NULL) You Can Combat computer stress!

(s) affected)

For more information about predicates, see WHERE.

Using a full outer join
To preserve mismatch information by including unmatched rows in the join results, use a full outer join.   microsoft®   SQL server& #8482; 2000 Complete OUTER JOIN operator full OUTER join, this operator includes all rows from two tables, regardless of whether another table has a matching value.

Suppose you join the authors table and the publishers table on the city column. The results show only the authors residing in the city where the publisher resides (in this case Abraham Bennet and Cheryl Carson). The SQL-92 full OUTER JOIN operator indicates that, regardless of whether there are matching data in the table, the result will include all rows from two tables.

To include all authors and publishers in the results, regardless of whether a publisher or publisher in the city lives in the same city, use a full outer join. The following is a query and result of a 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
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
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

(s) affected)

Use of the right outer join for connecting left outer joins within Oracle, (+) symbolic usage