Oracle data association query

Source: Saidi net Author: alizze

Associations are frequently used in Oracle Data Queries. Flexible application associations can solve many practical problems. The following are some examples:

Create a table

create table ab ( ab_id number(5), ab_name varchar2(30) ); create table bb ( bb_id number(5), bb_name varchar2(30) );

Insert data

insert into ab(ab_id,ab_name) values(1,'1ab_name'); insert into ab(ab_id,ab_name) values(2,'2ab_name'); insert into ab(ab_id,ab_name) values(3,'3ab_name'); insert into bb(bb_id,bb_name) values(1,'1bb_name'); insert into bb(bb_id,bb_name) values(2,'2bb_name'); insert into bb(bb_id,bb_name) values(4,'4bb_name'); insert into bb(bb_id,bb_name) values(6,'6bb_name');

Equivalent Association (full association)

select a.ab_id,a.ab_name,b.bb_name from ab a, bb b where a.ab_id=b.bb_id;

Returned results

AB_ID AB_NAME BB_NAME 1 1ab_name 1bb_name 2 2ab_name 2bb_name

Returns all records that can be matched in two tables. Records that cannot be matched are not returned.

Left join

Select AB _id, AB _name, bb_name From AB a left join BB B On a. AB _id = B. bb_id; Same Select AB _id, AB _name, bb_name From AB a, BB B Where a. AB _id = B. bb_id (+ );

Returned results

AB_ID AB_NAME BB_NAME 1 1ab_name 1bb_name 2 2ab_name 2bb_name 3 3ab_name

Return the left and right records of the table on the left. The right table fields with no corresponding records in the right table are blank.

Right join

Select AB _id, AB _name, bb_name From AB a right join BB B On a. AB _id = B. bb_id; Same Select AB _id, AB _name, bb_name From AB a, BB B Where a. AB _id (+) = B. bb_id;

Returned results:

AB_ID AB_NAME BB_NAME 1 1ab_name 1bb_name 2 2ab_name 2bb_name 4bb_name 6bb_name

Return all records in the right table. The fields in the left table are blank on the rows of records that cannot be matched in the right table.

Completely external Association

select ab_id,ab_name, bb_name from ab a full join bb b on a.ab_id=b.bb_id;

Returned results

AB_ID AB_NAME BB_NAME 1 1ab_name 1bb_name 2 2ab_name 2bb_name 3 3ab_name 6bb_name 4bb_name

Returns all the results of the two tables. If no corresponding record exists in the join condition, the column is blank.

The range of result sets returned by the full join operation is the smallest. The results returned by the full outer join operation have the largest range, and whether the left and right join operations are the same. The full join operation is the intersection of the left and right join operations.