Usage of the left Outer Join and right Outer Join in Oracle, (+) symbol usage

1. Internal Connection is simple

Select a. *, B. * from a, B where a. ID = B. ID
Select a. *, B. * from a inner join B on A. ID = B. ID
The above two statements are completely equivalent.

2. left Outer Join

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 statements are completely equivalent.

3. Right Outer Join

Select * from EMP a right join dept D on A. deptno = D. deptno
Select * from emp a, DEPT d Where a. deptno (+) = D. deptno
The above two statements are completely equivalent.

That is to say, as long as you change the table position in the statement, the right outer and left outer can play the same function.

 

 

 

 

"(+)" Can be used in Oracle, and 9i can use left/right/full outer join.

Left Outer Join: left Outer Join

Select E. last_name, E. department_id, D. department_name

From employees e

Left Outer Join orders ments d

On (E. department_id = D. department_id );

Equivalent

Select E. last_name, E. department_id, D. department_name

From employees e, departments d

Where E. department_id = D. department_id (+)

Result: Records of all employees and their respective departments, including records of employees who do not have the corresponding department number department_id.

Right outer join: Right Outer Join

Select E. last_name, E. department_id, D. department_name

From employees e

Right Outer Join orders ments d

On (E. department_id = D. department_id );

Equivalent

Select E. last_name, E. department_id, D. department_name

From employees e, departments d

Where E. department_id (+) = D. department_id

Result: Records of all employees and their respective departments, including records of departments without any employees.

Full outer join: Full outer join

Select E. last_name, E. department_id, D. department_name

From employees e

Full outer join orders ments d

On (E. department_id = D. department_id );

Result: Records of all employees and their respective departments, including employee records without the corresponding department number department_id and department records without any employees.

 

 

 

 

 

 

Full use of SQL--JOIN

Outer Join. Outer Join can be left Outer Join, right outer join, or complete external join.
When an external join is specified in the from clause, it can be specified 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 in the left table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

Right join or right outer join.
The right outer join is the reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.

Full join or full outer join.
The Complete External Join Operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

Rows are returned only when at least one row in the same two tables meets the join conditions. The inner join removes rows that do not match any row in the other table. Outer Join will return from
All rows of at least one table or view mentioned in the clause, as long as these rows comply with any where or having
Search criteria. Searches all rows in the left table referenced by the left Outer Join and all rows in the right table referenced by the right outer join. All rows of the two tables in the complete external join will be returned.

Microsoft & reg; SQL Server & #8482; 2000 use 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 Outer Join syntax for the SQL-92 and the use of * = and = * in the WHERE clause *
Operator specifies the old Syntax of Outer Join. Since the SQL-92 syntax is not prone to ambiguity, the legacy Transact-SQL
Outer Join is sometimes ambiguous, so it is recommended to use the SQL-92 syntax.

Use left Outer Join
Assume that the authors table and the publishers table are joined in the city column. The results only show the author of the publisher's city (Abraham Bennet and Cheryl Carson in this example ).

To include all authors in the results, regardless of whether the publisher lives in the same city, use the SQL-92 to connect left out. The following are the query results of the left Outer Join of transact-SQL:

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

The following is the result set:

Au_fname au_lname pub_name
-------------------------------------------------------------------
Reginald blotchet-Hils null
Micel 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

(23 row (s) affected)

Whether or not it matches the city column in the publishers table, left Outer Join will contain
All rows in the authors table. Note: most authors in the results do not have matched data. Therefore, the pub_name column of these rows contains null values.


Use Right Outer Join
Assume that the authors table and
Publishers table. The result only shows the author who lives in the publisher's city (in this example, Abraham Bennet and Cheryl
Carson ). SQL-92 right Outer Join operator right Outer Join
Indicates that no matter whether the first table has matched data, the result will contain all rows in the second table.

To include all publishers in the results, regardless of whether there are publishers in the city, use the SQL-92 to connect right out. The following are the queries and results of the right Outer Join of transact-SQL:

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

The following 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 with constants) can further limit outer joins. The following example contains the same right outer join, but removes the title of a book with sales volume less than 50:

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

The following is the result set:

Stor_id qty title
----------------------------------------------------------------------
(Null) but is it user friendly?
(Null) Computer phobic and non-phobic individuals: Behavior
Variations
(Null) cooking with computers: surreptitious balance sheets
(Null) emotional security: a new algorithm
(Null) Partition ty years in Buckingham palace kitchens
7066 75 is anger the enemy?
(Null) life without fear
(Null) net etiquette
(Null) onions, leeks, and garlic: Cooking secrets of
Mediterranean
(Null) prolonged data Deprivation: four case studies
(Null) secrets of Silicon Valley
(Null) Silicon Valley gastronomic treats
(Null) Straight Talk About computers
(Null) sushi, anyone?
(Null) the busy executive's database Guide
(Null) The Gourmet microwave
(Null) the psychology of computer cooking
(Null) You can combat computer stress!

(18 row (s) affected)

For more information about predicates, see where.

Use a complete external connection
To retain the unmatched information by including unmatched rows in the join results, use the complete external join. Microsoft & reg; SQL
Server & #8482; 2000 provides the complete external join operator full outer
Join, regardless of whether the other table has a matched value, this operator includes all rows in the two tables.

Assume that
Join the authors table and publishers table on the column. The result only shows the author who lives in the city where the publisher is located (in this example, Abraham
Bennet and Cheryl Carson ). SQL-92 full outer join
Operator: no matter whether the table has matched data, the result will include all rows in the two tables.

To include all authors and publishers in the results, regardless of whether there are publishers or publishers in the city living in the same city, use a complete external connection. The following are the query results of the Complete External join of transact-SQL:

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

The following is the result set:

Au_fname au_lname pub_name
--------------------------------------------------------------------
Reginald blotchet-Hils null
Micel 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

(30 row (s) affected)