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

MySQL learning footprint record 14 -- table alias and self-linked bitsCN.com

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

Tables used in this query:

The following table num represents A bus route, such as A 1-way vehicle, A 2-way vehicle, and A stop with A table parking site, such as station A and Station B.

Table:

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

Prerequisites

1. use the table alias

* The Table alias is used only during query execution.

* The Table alias is not returned 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:

I believe that it is effort-free to understand table aliases. similar to column aliases, table aliases only give the table another name, representing the same table.

2. auto join

* Auto-join is usually used as an external statement to replace the subquery statement used to retrieve data from the same table.

* You can use the same table alias multiple times in a single statement.

The following is a simple example to help you 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)

Resolution:

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 one. The two different tables r1, r2

Our goal is to query all bus lines that share the same station, so we 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.

BitsCN.com