database connections within connections, natural connections, outer joins differentiated

The connection in the database is divided into: Inner connection, natural connection, outer connection; (outer connection is also divided into: Left outer connection, right outer connection and full outer connection)

Natural connection (natural join):

A natural connection is a special equivalence connection that requires the same attribute columns to be compared in two relational tables, without adding join conditions, and eliminating duplicate attribute columns in the results.

Internal connection (inner join):

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.

(The inner join is basically the same as the natural connection, except that the natural connection requirement is a comparison of the property column with the same name, while the inner join does not require two property columns with the same name, you can use on to specify the same join condition for a two-column field)

Natural connection, the internal connection when some property values are different will cause these tuples will be discarded, how to save these will be lost information, external connection solves the corresponding problem.

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 row 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,TESTC,TESTD each have a, b two columns

TESTB has a,c two columns

First make a full table query (Cartesian product)
SELECT * from TESTA,TESTB;

One, natural connection

SELECT * from TESTA NATURAL JOIN testb;

Two, internal connection: Here we are demonstrating the equivalent connection

Mode one: SELECT * from TESTA A, testc c WHERE a.a=c.a;

Mode two: SELECT * from TESTA a INNER joins TESTC C on (A.A=C.A);

The results table is as follows:

Three, outer connection:

1. Left Outer connection 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:

Notation One: SELECT * from TESTA a left OUTER JOIN TESTC C on (A.A=C.A);

Notation Two: SELECT * from TESTA a left JOIN TESTC C on (A.A=C.A);

Oracle-specific notation: SELECT * from TESTA A,TESTC C WHERE a.a=c.a (+);

The results table is as follows:

2. Right outer connection outer JOIN or Starboard join

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

Notation One: SELECT * from TESTA a right OUTER JOIN TESTC C on (A.A=C.A);

Notation Two: SELECT * from TESTA a right JOIN TESTC C on (A.A=C.A);

Oracle-specific notation: SELECT * from TESTA A,TESTC C WHERE a.a (+) =c.a;

The results table is as follows:

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.

Notation One: SELECT * from TESTA a full OUTER JOIN TESTC C on (A.A=C.A);

Notation Two: SELECT * from TESTA a full JOIN TESTC C on (A.A=C.A);

Equivalent notation:

SELECT * from TESTA a left JOIN TESTC C on (A.A=C.A)

UNION

SELECT * from TESTA a right joins TESTC C on (A.A=C.A);

The results table is as follows:

database connections within connections, natural connections, outer joins differentiated