MySQL learning footprint record 14-table alias and self-join

MySQL learning footprint record 14-table alias and self-join table used in this query: The following table num represents the bus route, such as 1 car, 2 car, stop with table parking site, such as Station A and Station B:

route;+------+------+| num  | stop |+------+------+|    1 | A    ||    1 | B    ||    1 | C    ||    2 | B    ||    2 | C    ||    2 | D    |+------+------+

 

Prerequisites 1. Use the table alias * The table alias only uses the * table alias during query execution and does not return to the client.

  Example:  mysql> SELECT * from route AS r1           -> WHERE r1.num = 1;+------+------+| num  | stop |+------+------+|    1 | A    ||    1 | B    ||    1 | C    |+------+------+3 rows in set (0.00 sec)

 

Resolution: it is believed that it is not necessary to understand the table alias. Similar to the column alias, the table alias only gives the table another name, representing the same table 2. self-join * Self-join is usually used as an external statement to replace the subquery statement used to retrieve data from the same table * using the table alias provides a simple example of using the same table multiple times in a single statement. examples help to understand the principle of auto-join Example: the following statement queries all bus lines at the same station.

mysql> SELECT DISTINCT r2.num,r2.stop         -> FROM route AS r1,route AS r2         -> WHERE r1.stop = r2.stop          -> ORDER BY r2.stop;+------+------+| num  | stop |+------+------+|    1 | A    ||    1 | B    ||    2 | B    ||    1 | C    ||    2 | C    ||    2 | D    |+------+------+6 rows in set (0.00 sec)

 

Explanation: First sentence

mysql> SELECT DISTINCT r2.num,r2.stop                    -> FROM route AS r1,route AS r2；

 

To explain the problem, remove the DISTINCT keyword and only extract the following clause.

 mysql> select * From route AS r1,route r2;+------+------+------+------+| num  | stop | num  | stop |+------+------+------+------+|    1 | A    |    1 | A    ||    1 | B    |    1 | A    ||    1 | C    |    1 | A    ||    2 | B    |    1 | A    ||    2 | C    |    1 | A    ||    2 | D    |    1 | A    ||    1 | A    |    1 | B    ||    1 | B    |    1 | B    ||    1 | C    |    1 | B    ||    2 | B    |    1 | B    ||    2 | C    |    1 | B    ||    2 | D    |    1 | B    ||    1 | A    |    1 | C    ||    1 | B    |    1 | C    ||    1 | C    |    1 | C    ||    2 | B    |    1 | C    ||    2 | C    |    1 | C    ||    2 | D    |    1 | C    ||    1 | A    |    2 | B    ||    1 | B    |    2 | B    ||    1 | C    |    2 | B    ||    2 | B    |    2 | B    ||    2 | C    |    2 | B    ||    2 | D    |    2 | B    ||    1 | A    |    2 | C    ||    1 | B    |    2 | C    ||    1 | C    |    2 | C    ||    2 | B    |    2 | C    ||    2 | C    |    2 | C    ||    2 | D    |    2 | C    ||    1 | A    |    2 | D    ||    1 | B    |    2 | D    ||    1 | C    |    2 | D    ||    2 | B    |    2 | D    ||    2 | C    |    2 | D    ||    2 | D    |    2 | D    |+------+------+------+------+36 rows in set (0.00 sec)

 

From the preceding query results, we can see that there are 36 records in total (just the Cartesian product of two tables. For Cartesian Product, click MySQL learning footprint record 13 -- join table ), it can be inferred that the so-called auto join is to regard the same table as an independent table. The two different tables r1 and r2 are used to query all bus lines sharing the same station, therefore, you can add filtering conditions from the result set in the table above (to make the station equal ):

r1.stop = r2.stopmysql> SELECT * FROM route AS r1,route AS r2        -> WHERE r1.stop = r2.stop；    +------+------+------+------+| num  | stop | num  | stop |+------+------+------+------+|    1 | A    |    1 | A    ||    1 | B    |    1 | B    ||    2 | B    |    1 | B    ||    1 | C    |    1 | C    ||    2 | C    |    1 | C    ||    1 | B    |    2 | B    ||    2 | B    |    2 | B    ||    1 | C    |    2 | C    ||    2 | C    |    2 | C    ||    2 | D    |    2 | D    |+------+------+------+------+10 rows in set (0.00 sec)

 

This is very close to the expected result, but we only need to add the num and stop columns.

SELECT r2.num, r2.stop (SELECT r1.num, r1.stop) mysql> SELECT r2.num, r2.stop-> FROM route AS r1, route r2-> WHERE r1.stop = r2.stop; + ------ + | num | stop | + ------ + | 1 | A | 1 | B | 1 | B | 1 | C | 1 | C | | 2 | B | 2 | B | 2 | C | 2 | C | 2 | D | + ------ + 10 rows in set (0.00 sec)

 

Finally, the same records are removed and sorted by station.

mysql> SELECT DISTINCT r2.num,r2.stop          -> FROM route AS r1,route AS r2          -> WHERE r1.stop = r2.stop          -> ORDER BY r2.stop;+------+------+| num  | stop |+------+------+|    1 | A    ||    1 | B    ||    2 | B    ||    1 | C    ||    2 | C    ||    2 | D    |+------+------+6 rows in set (0.00 sec)

 

From the result set, we can see that 1-way vehicles and 2-way vehicles share B and C stations.