MySQL self-connect

The self-join in the SELECT statement.
So far, we've been connecting two different tables, so can we make a self-connection to a single table? The answer is yes.
Is it necessary to make a self-connection to a single table? The answer is yes.

Alias of table:
A table can connect itself. We need a mechanism to differentiate two instances of a table when we are self-connected.
In the FROM clause (clause) we can give the table a different alias, and then in the other needs of the statement to use the alias location
Use dot (dot) to connect the alias and field name.

We also give two tables here to explain the self-connection.
Edinburg Bus Line,

Station table:
Stops (ID, name)

Bus Line table:
Route (NUM, company, POS, stop)

First, the bus route table route is self-connected.

SELECT * from Route R1, Route R2
WHERE R1.num=r2.num and R1.company=r2.company

Our route table uses fields (num, company) to self-connect. What does the result mean?
You can get to know any two connecting stations of each bus line.

Second, use the Stop field to self-connect the route (bus Line table).

SELECT * from Route R1, Route R2
WHERE R1.stop=r2.stop;

The result of the query is that all bus lines are shared with the same station. Does this result make a lot of sense to the transfer?

From these two examples we can see that the self-connected grammatical structure is simple, but the semantic results are often not
So easy to understand. The two tables listed here, if used properly, can solve many practical problems,
For example, how to transfer between any of the 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 '

MySQL self-connect