Oracle internal connection, outer connection, right outer connection, full outer connection small summary

Database version:Oracle 9i

Table TESTA,TESTB,TESTC, each with A, B two columns

a	b
001
002

a	b
001
003

A	B
001	10C
004	40C

There are two types of connections: inner and outer connections.

A . Internal Connection

Internal connections, the most common equivalent connection, for example:

SELECT *
from Testa,testb
WHERE TESTA. A=testb. A

Results

A	B	A	B
001	10A	001	10B

B. External Connection

The outer connection is divided into left outer connection, right outer connection and full outer connection.

1. left outer join outer JOIN or Ieft Join

The left OUTER join is the addition of unmatched data from the primary table on the basis of the equivalent connection, for example:

SELECT *
from TESTA
Left OUTER JOIN Testb
on TESTA. A=testb. A

Oracle supports an alternative notation

SELECT *
from Testa,testb
WHERE TESTA. A=testb. A (+)

Results:

001

TD valign= "Top" width= "page" >

a	b	a	b
10a	001	10b
002	20a

Three tables do left outer joins

SELECT *
from TESTA
Left OUTER JOIN Testb
on TESTA. A=testb. A
Left OUTER JOIN Testc
on TESTA. A=TESTC. A

Another form of Oracle support

SELECT *
from Testa,testb,testc
WHERE TESTA. A=testb. A (+)
and TESTA. A=TESTC. A (+)

Results:

a	b	a	b	b
001	10A	001	10B	001	10C
002	20A

2. right outer connection outer JOIN

The right outer join is based on the equivalent connection and the mismatched data of the connected table is added.

SELECT *
from TESTA
Right OUTER JOIN Testb
on TESTA. A=testb. A

Another way that Oracle supports

SELECT *
from Testa,testb
WHERE TESTA. A (+) =testb. A

Results:

A	B	A	B
001	10A	001	10B
		003	30B

3. full out -of-the-outer JOIN or complete join

A full outer join is a combination of unmatched data from the left and right tables on the basis of an equivalent join.

SELECT *
from TESTA
Full OUTER JOIN Testb
on TESTA. A=testb. A

The equivalent of the whole outer join, the same table first left connected, and then right connected

SELECT testa.*,testb.*
from TESTA
Left OUTER JOIN Testb
on TESTA. A=testb. A
UNION
SELECT testa.*,testb.*
from Testb
Left OUTER JOIN TESTA
on TESTA. A=testb. A

Results:

A	B	A	B
001	10A	001	10B
002	20A
		003	30B

Oracle internal connection, outer connection, right outer connection, full outer connection small summary