Table TESTA,TESTB,TESTC, each with a, b two columns
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
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