Out-of-database connections inside connections

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

A	B
001	10A
003	20A

A	B
001	10B
002	30B

A	B
001	10C
002	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. 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