Section 8 Self-join in SELECT statements

Section 8 Self-join in select statements

So far, we have connected two different tables. can we establish a self-connection for a table? 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 self-Join Operation.
In the from clause, we can get different aliases for this table, and then use the alias in other places of the statement.
Use dot 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)

For more detailed explanations of the two tables, refer to here: http://sqlzoo.cn/buses.htm

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

In the route table, fields (Num, company) are used for 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 self-join syntax structure is very simple, but the semantic result is often not
So easy to understand. The two tables listed here can solve many practical problems if they are used properly,
For example, 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'

For more self-join exercises, go to the http://sqlzoo.cn/6.htm to practice.