data queries involving two of tables in a database are typically implemented using a connection method. Connections are divided into inner and outer connections.
Internal connection:
The connection result contains only the rows that meet the join criteria, and the two tables participating in the connection should meet the join criteria.
External connection:
The result of the join contains not only rows that meet the join criteria but also those that do not meet the criteria themselves. Includes left outer joins, right outer joins, and full outer joins.
Left Outer connection:
Left table data rows are reserved, the right table holds rows that match the join criteria
Right outer connection:
The right table data row is reserved, the left table holds the rows that match the join criteria
Full Outer connection:
LEFT OUTER JOIN Union right OUTER JOIN
Example:
Table TESTA,TESTB,TESTC, each with a, b two columns
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
B. Outer 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:
A |
B |
A |
B |
001 |
10A |
001 |
10B |
002 |
10B |
|
|
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 |
a |
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 |
Out-of-database connections inside connections