Oracle table associated applications

Association is the most useful in Oracle Data Query. Flexible applications can solve many practical problems. The following are some examples:

1. 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 ');

2. Equivalent Association (full association)
   Select a. AB _id, A. AB _name, B. bb_name FROM AB,
   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.
3. 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.

4. 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.
5. 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.