Mysql self-connection learning notes

Mysql self-join queries some data first Based on the condition and then returns the returned value to query some data. This is mysql self-join. Of course, left join can also be used, next let's take a look at the usage of mysql self-connection.

The auto-join processing speed of mysql is much faster than that of subqueries. Therefore, it is necessary to learn self-join.

Common SQL subquery statements

The Code is as follows:	Copy code
SELECT 'id', 'bic 'FROM 'biao' WHERE 'id' = (SELECT 'id' FROM 'biao' WHERE 'id' = '9696e ');

Mysql self-join SQL statement

The Code is as follows:	Copy code
SELECT 'id', 'bic 'FROM 'biao' AS a, 'biao' AS B WHERE. id = B. id AND B. id = 'www. bKjia. c0m ';

Self-join SQL statements look refreshing and elegant.

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.

The Code is as follows:	Copy code
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'