Out-of-database connections inside connections

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.