Simple and easy to understand--explain the similarities and differences of inner Connection + outer connection + RIGHT outer connection and full outer connection

Table TESTA,TESTB,TESTC, each with a, b two columns

A	B
001	10A
002	20A

A	B
001	10B
003	30B

A	B
001	10C
004	40C

The connection is divided into two types: inner and outer connections.

A INNER JOIN

The inner connection, that is, 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. Outer Joins

Outer joins are divided into left outer joins, right outer joins and whole outer joins.

1. Left OUTER JOIN outer JOIN or Ieft Join

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

SELECT * from Testa left OUTER JOIN testb on Testa. A=testb. A

Oracle supports another way of writing

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

Results:

A	B	A	B
001	10A	001	10B
002	20A

Three tables for left outer joins

SELECT * FORM Testa left OUTER JOIN testb on Testa. A=testb. A left OUTER JOIN testc on Testa. A=TESTC. A

Another way of writing Oracle support

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

Results:

A	B	A	B	A	B
001	10A	001	10B	001	10C
002	20A

2. Right outer join or right-hand join

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

SELECT * from Testa right OUTER JOIN testb on Testa. A=testb. A

Another way of writing Oracle support

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

Results:

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

3. Fully outer JOIN or full join

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

SELECT * from Testa full OUTER JOIN testb on Testa. A=testb. A

Equivalent writing of all outer joins, make a left connection to the same table, and then connect right

SELECT testa.*,testb.* from Testa left OUTER JOIN testb on Testa. A=testb. A
UNION