Oracle---->join

The recent use of ODI, where a join component involves several common types of joins, organizes the differences between the lower cross-joins and natural joins.

I. Overview

A 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 is formed

Ii. connection Types and examples

There are 4 basic types of connections, Inner,outer,natural,cross connections

2.1 Internal Connection ( INNER JOIN =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.

-- ---internal connection SELECT *  from INNER JOIN  on a.person_id= b.person_id;

2.2 External connection (outer join)

Outer joins, returned to the query result 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).

1. The 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;

- left Connection SELECT *  from  Left JOIN  on a.person_id=  b.person_id; -- oracle9i The previous version of the left connection is as follows: SELECT *  from where a.person_id= b.person_id (+);

2. The 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;

-- -Right connection of outer connection species SELECT *  from  Right JOIN  on a.person_id=  b.person_id; -- -oracle9i The previous version of the left connection is as follows: SELECT *  from where a.person_id (+)= b.person_id;

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

-- --full Join SELECT *  from  Full JOIN  on a.person_id= b.person_id;

2.3 Natural Connections ( 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. A natural connection does not contain duplicate properties. A natural connection is done by taking all attributes that have the same name in the participating table relationships (that is, equality comparisons), so you do not need to add the join condition yourself

SELECT  from JOIN B

SQL error: ora-25155: The column used in the NATURAL join cannot have a qualifier

-- --Natural connection SELECT *  from JOIN B

2.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.

-- -Cross Connect SELECT *  from  Cross JOIN B

Report:

CREATE TABLE" SCOTT". " A "(" person_id ") Number(5)NULL, "Person_name"VARCHAR2(255BYTE)NULL );-- ------------------------------Records of A-- ----------------------------INSERT  into"SCOTT". " AVALUES('1','Zhang San');INSERT  into"SCOTT". " AVALUES('2','John Doe');INSERT  into"SCOTT". " AVALUES('3','Harry');INSERT  into"SCOTT". " AVALUES('4','Zhao Liu');INSERT  into"SCOTT". " AVALUES('5','Zhou Qi');CREATE TABLE" SCOTT". " B "(" person_id " ) Number(5)NULL, "Love_fruit"VARCHAR2(255BYTE)NULL );-- ------------------------------Records of B-- ----------------------------INSERT  into"SCOTT". " BVALUES('1','Banana');INSERT  into"SCOTT". " BVALUES('2','Apple');INSERT  into"SCOTT". " BVALUES('3','Orange');INSERT  into"SCOTT". " BVALUES('4','Pear');INSERT  into"SCOTT". " BVALUES('8','Peach');

Oracle---->join