Oracle Connectivity Summary (internal, external, natural, cross-connect, self-connected)

Never give up, everything is possible!!!

Only to find a way to succeed, not to find excuses for failure!

Oracle Connection Summary (internal, external, natural, cross-connect, self-connected) 1. Brief introduction  1) The connection of two tables is established by linking one or more columns in one table to columns in another table. The expressions used to connect two tables make up the join condition. When the connection succeeds, the data in the second table is connected to the first table, and a composite result set 2) has 5 basic types of connection, Inner,outer,natural,cross connection, self-connection. 2. Description and examples

1) Inner connection (inner join (can be abbreviated as join))

The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns.

1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the concatenated columns, and the query results list all the columns in the joined table, including the duplicate properties. (different from natural connections)

2, unequal connections: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.

Teacher and student table EG1: Equivalent connections:

SELECT S.*, T.//Note Oracle Renaming table cannot be added as just a space, as can be used to rename columns 

Result: (Note that the result contains duplicate attributes, which are renamed in duplicate attribute results)

EG2:

On STUDENT. DEPTNO= TEACHER. DEPTNO;

2) Natural Connection (natural join)

The natural connection is to select the same name attribute on the generalized Cartesian product RxS to match the equal conditional tuple, then the projection, remove duplicate the same name attribute, compose the new relation. That is, the natural connection is to look for those fields that have the same data type and column names in both tables, and then automatically connect them and return all results that match the criteria.

EG1:

From STUDENT  JOIN TEACHER;

Error: (Can not be added to limit conditions)

SQL Error: ORA-25155: Columns used in NATURAL joins cannot have qualifiers 25155. 00000 -" Column used in NATURAL join  cannot has qualifier "*cause:columns that is used for a Named-join (either a NATURAL join or a join with a USING clause) cannot has an explicit qualifier. *action:remove the qualifier.         

EG2:

From STUDENT  JOIN TEACHER;

Analysis: Two tables have two identical properties, Teacherid and Deptno, which are connected by the two fields without qualification

Result: (Note deptno attribute only one)

The following contrasts the results of using the INNER JOIN: (contains duplicate attributes, and the connection method differs from the natural connection)

On STUDENT. DEPTNO= TEACHER. DEPTNO;

3) External connection (outer join)

Outer joins, returning to the query results collection contain not only rows that meet the join criteria, but also all data rows in the left table (left OUTER join or left join), right table (right outer join or RIGHT join), or two edge table (full outer join).

The 1.left join (left join) is equivalent to (outer join) returns records that include all records in the left table and the equivalent of the junction fields in the right table;

The 2.right join (right join) is equivalent to (outer join) returns records that include all records in the right table and the equivalent of the junction fields in the left table;

3. Full join (fully connected) is equivalent to (outer join) query result equals left OUTER join and right outer join and

EG1:

SELECT  S.*, T.= T.class; 

Output: (contains all records in student and records that meet the criteria)

EG2:

SELECT  S.*, T.= T.class; 

Results:

EG3:

SELECT  S.*, T.= T.class; 

Output: (because there are corresponding student in teacher, so the result is the same as the left join) 4) Cross join

The cross join does not have an ON clause, it returns the Cartesian product of all data rows of the two connected tables, and the number of rows returned to the result set is equal to the number of data rows in the first table that meet the query criteria multiplied by the number of data rows in the second table that meet the query criteria.

EG1:

SELECT  S.*, T.JOIN TEACHER t; 

Equivalent to:

SELECT  S.*, T. FromSTUDENT S, TEACHER  t; 

Output Result:

5) Self-connect

Connected tables are the same table, and using a self-join allows one mirror of its own table to be treated as another table, allowing for some special data.

Example of Use: Calculate Transit link transfer problem (see EG2)

EG1:

SELECT  S1.  *, S2. WHERE S1. DEPTNO andS1. Classno=s2. Classno;   

Results:

EG2:

Table Route (NUM, company, POS, stop)

WHERE r1.stop=r2.stop;

All routes of the same bus station can be queried

3. Summary

1) Even if the connection condition is not satisfied, the outer join returns rows in the result set, and the inner join does not return a row in the result set class when the join condition is not satisfied, the column in a table is usually returned, but the second table does not have a return value-null

2) Natural connections do not contain duplicate properties

3) Cross connection is two table data Cartesian product (equivalent to no join condition)

Oracle Connectivity Summary (internal, external, natural, cross-connect, self-connected)