Oracle join usage demonstration

The following articles mainly introduce the join usage in Oracle. If you are a beginner, you are very interested in the actual usage of oin in Oracle, however, if you cannot find any information to learn more about it, you may wish to read the following articles.

Oracle join usage

8i:

 
 

  
  
create table dali.test1(a int,b int);  
  
  
create table dali.test2(a int,b int);  
  
  
insert into dali.test1 values(1,456);  
  
  
insert into dali.test1 values(2,427);  
  
  
insert into dali.test2 values(1,45456);  
  
  
insert into dali.test2 values(3,45656);  
 
 

Internal Connection

 
 

  
  
select * from dali.test1 a, dali.test2 b where a.a=b.a; 
 
 

Left join

 
 

  
  
select * from dali.test1 a, dali.test2 b where a.a=b.a(+); 
 
 

Right join

 
 

  
  
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a; 
 
 

Full connection

 
 

  
  
select * from dali.test1 a, dali.test2 b where a.a=b.a(+)  
  
  
union  
  
  
select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;  
 
 

Dikar

 
 

  
  
select * from dali.test1, dali.test2; 
 
 

9i is the same as sqlserver left join, right join, full join

Divided into 1. inner join 2. left join 3. right join 4. LEFT OUTER JOIN

In Oracle join implementation, we first set two tables to be used.

Table A Table B

ID NAME ID CLASS

1 IBM 1 C1

2 SONY 3 C3

3 BMW 4 C4

1 inner join: SELECT * from a inner join B on a. ID = B. ID

Table ID NAME CLASS

1 IBM C1

3 BMW C3

By the way, the IDs of both tables exist and the combined tables of the two tables are obtained.

2 left join: SELECT * from a left join B on a. ID = B. ID

Table ID NAME CLASS

1 IBM C1

2 SONY null

3 BMW C3

If there is A sense of direction, LEFT (left) returns A (LEFT table of left Statement) and table B does not have any records! Haha)

3 right join: SELECT * from a right join B on a. ID = B. ID

Table ID NAME CLASS

1 IBM C1

3 BMW C3

4 null C4

RIGHT (right) returns all records of B (table on the RIGHT side of the right statement). If the records of Table A do not exist, they must be completed (I have turned over too! Haha)

4 full outer join: SELECT * from a full outer join B on a. ID = B. ID

Table ID NAME CLASS

1 IBM C1

2 SONY null

3 BMW C3

4 null C4

The above content is an introduction to the join usage in Oracle. I hope you will get some benefits.