Differences between left and right full connections in oracle external connections

Oracle statement:

1 from join_table join_type join_table2 [ON (join_condition)]

Join_table: name of the table to join
Ioin_tyoe: Connection Method

There are three types:

Internal Connection: (1) equivalent connection:

(2) natural connection: Remove duplicate rows from the equivalent connection, that is, the natural connection.

(3) Unequal connections: query by non-equal signs, including>,> =, etc.

　　　　　　

Outer join: (1) left outer join (left outer join or left join). In addition to meeting the join conditions, all rows in the left table of join are returned;

(2) right outer join (right outer join or right join); also includes all rows in the right table of join;

(3) full outer join (full outer join or full join );
Outer Join lists all data rows that meet the search criteria in the left table (left Outer Join), right table (right Outer Join), or two tables (all Outer Join.

Cross join: (cross join) returns the Cartesian product of all data rows in the table without the where clause.

1. Why use external connections:

The returned query results include only the rows that meet the query conditions (where search conditions or having conditions) and connection conditions.

The inner join removes the rows that do not match any row in the other table. The outer join expands the inner join result, except that all matched rows are returned, some or all of the unmatched rows are returned, depending on the type of the outer join.

2. When will external connections be used:

For example:

Information classified storage, such as the employee information table. The salary collection status of each month is in another table. In this case, external join is required for table Association.

If you want to use an intranet connection for query, some employee-related information that does not receive the salary may be removed, but we want to get the employee's salary, this means you can see from the query results which are not collected and which employees have settled. in this way, an external connection is required.

3. Can both external connections be used?

 

4. Example: Compare the query results of left and right connections.

Tool used: SQL Navigator 3 + Oracle 8.0

Create a Chinese renewal table:

1 create table testyw -- 文2 2 (3 id number, 4 name varchar (40), 5 scoreyw number6)

Create a mathematical sequence table:

1 create table testsx -- Mathematical second table 2 (3 id number, 4 name varchar2 (40), 5 scoresx number6)

Insert statement row by row

1 insert into testyw values (201, 'wheat ', 87) 2 insert into testyw values (202, 'White', 98) 3 insert into testyw values (203, 'black Black ', 82) 4 insert into testsx values (202, 'xiaobai', 81) 5 insert into testsx values (203, 'black', 67)

The following table information is queried after the insert operation is complete:

1 select * from testyw -- Chinese orders table

 

1 select * from testsx -- Mathematical orders table

Query a table:

Query results of the left (outer) connection:

1 select * from testyw y left join testsx s on (y. id = s. id) 2 -- or 3 select * from testyw y, testsx s where y. id = s. id (+) 4 -- "(+)" The Other Side of the location is the connection direction. 5 -- all records on the left side of the equal sign are displayed, regardless of whether they are matched on the right side.

 

 

Query results of right (outer) connections:

1 select * from testyw y right join testsx s on (y. id = s. id) 2 -- or 3 select * from testyw y, testsx s where y. id (+) = s. id4 -- the other side of the "(+)" location is the connection direction. 5 -- all records on the right of the equal sign will be displayed, regardless of whether they are matched on the left.

 

Full join query results:

1 select * from testyw y full join testsx s on (y. id = s. id) 2 -- remember, the full connection cannot be the same as the left and right connections with "(+)". There is only one method here.

 

 

Related Links: Oracle external connections and self-connections