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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.