Oracle join usage

The following content describes the usage of Oracle join. If you have any questions about Oracle join, you can use the following articles to understand its practical application and functions, the following is a detailed introduction of the article. I hope you will gain some benefits after browsing the following content.

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; 
 
 

In Oracle join usage, 9i and sqlserver are the same as left join, right join, and full join.

Divided into 1.

 
 

  
  
INNER JOIN 2. LEFT JOIN 3. RIGHT JOIN 4.LEFT OUTER JOIN 
 
 

First, set the two tables we want to use.

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

In Oracle join usage, the related operations are complex. If there is A sense of direction, you can use LEFT to obtain all records of A (table on the left of the LEFT statement), and make up if the corresponding records of Table B do not exist.

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 usage of Oracle join. I hope you will find some gains.