Oracle _ multi-table join

Stealing: http://www.blogjava.net/hello-yun/archive/2011/04/08/347890.html

Table TESTA, TESTB, and TESTC have two columns, A and B:

TESTA (A, B) | TESTB (A, B) | TESTC (A, B)

001 10A | 001 10B | 001 10C

002 20A | 003 30B | 004 30C

 

There are two types of connections: internal connection and external connection.

 

A. Internal Connection:

 

Internal Connection, that is, the most common equivalent connection, for example:

 

SELECT*
FROMTESTA, TESTB
WHERETESTA. A = TESTB.

Result:

TESTA (A, B) | TESTB (A, B)

001 10A | 001 10B

 

B.External Connection

 

External connections include left outer connections, right outer connections, and full outer connections.

 

1.Left Outer JoinLeft outer join or left join

 

The left Outer Join adds unmatched data in the master table based on the equijoin. For example:

 

SELECT*
FROMTESTA
LEFT OUTER JOINTESTB
ONTESTA. A = TESTB.

 

Oracle supports another method

 

SELECT*
FROMTESTA, TESTB
WHERETESTA. A = TESTB. A (+)

 

Result:

TESTA (A, B) | TESTB (A, B)

001 10A | 001 10B

002 20A |

 

Three tables for left Outer Join

 

SELECT*
FROMTESTA
LEFT OUTER JOINTESTB
ONTESTA. A = TESTB.
LEFT OUTER JOINTESTC
ONTESTA. A = TESTC.

 

Another statement supported by Oracle

 

SELECT*
FROMTESTA, TESTB, TESTC
WHERETESTA. A = TESTB. A (+)
ANDTESTA. A = TESTC. A (+)

 

Result:

TESTA (A, B) | TESTB (A, B) | TESTC (A, B)

001 10A | 001 10B | 001 10C

002 20A |

 

2.Outer right connectionRight outer join or right join

 

The outer right join adds unmatched data in the connected table on the basis of the equijoin.

 

SELECT*
FROMTESTA
RIGHT OUTER JOINTESTB
ONTESTA. A = TESTB.

 

Another method supported by Oracle

 

SELECT*
FROMTESTA, TESTB
WHERETESTA. A (+) = TESTB.

 

Result:

 

TESTA (A, B) | TESTB (A, B)

 

001 10A | 001 10B

| 003 20B

 

3.All external connections Full outer join or full join

 

The full outer join adds unmatched data in the left and right tables on the basis of the equivalent join.

 

SELECT*
FROMTESTA
FULL OUTER JOINTESTB
ONTESTA. A = TESTB.

 

Equivalent method of full outer join. Perform left join and right join on the same table first.

 

SELECTTESTA. *, TESTB .*
FROMTESTA
LEFT OUTER JOINTESTB
ONTESTA. A = TESTB.
UNION
SELECTTESTA. *, TESTB .*
FROMTESTB
LEFT OUTER JOINTESTA
ONTESTA. A = TESTB.

 

Result:

 

TESTA (A, B) | TESTB (A, B)

 

 

001 10A | 001 10B

002 20A |

| 003 20B