How does one perform Oracle SELECT statement auto-join?

The following articles mainly introduce the actual operation steps for the Oracle SELECT statement auto-join in the SQL tutorial. Until now, we have connected two different related tables, have you ever wondered whether you can establish a self-connection to one of the tables? The answer is yes.

Is it necessary to connect a table? The answer is yes.

Table alias:

A table can be connected by itself. We need a mechanism to differentiate two instances of a table during the auto-join of the Oracle SELECT statement.

In the FROM clause), we can get different aliases for the table, and then use dot points for other places where the alias needs to be used in the statement) to connect the alias and field name.

Here we also provide two tables to explain the self-join.

Edinburgh bus line,

Station table:

 
 

  
  
stops(id, name) 
 
 

Bus Route table:

 
 

  
  
route(num, company, pos, stop) 
 
 

1. Perform a self-connection to the route table of the bus route.

 
 

  
  
SELECT * FROM route R1, route R2 WHERE R1.num=R2.num AND R1.company=R2.company 
 
 

We use the field (num, company) in the route table to perform the Oracle SELECT statement self-join. What does the result mean?

You can know any two interconnected stations on each bus line.

2. Use the stop field to establish a self-connection to the route bus route table.

 
 

  
  
SELECT * FROM route R1, route R2 WHERE R1.stop=R2.stop; 
 
 

The query result is all bus lines that share the same station. Does this result make sense for the transfer.

From the two examples, we can see that the syntax structure of the Oracle SELECT statement self-join is very simple, but the semantic results are often not so easy to understand. The two tables listed here can solve many practical problems if they are used properly, such as how to transfer between any two sites.

 
 

  
  
SELECT R1.company, R1.num FROM route R1, route R2, stops S1, stops S2  
  
  
WHERE R1.num=R2.num AND R1.company=R2.company AND R1.stop=S1.id AND R2.stop=S2.id  
  
  
AND S1.name='Craiglockhart' AND S2.name='Tollcross'   
 
 

The above content is an introduction to the Oracle SELECT statement self-join in a simple SQL tutorial. I hope you will gain some benefits.