A one-to-multiple table is usually associated with the primary key of another table. However, there are also some unusual situations, that is, two tables are not joined by the primary key of one of the tables.
For example:
Copy codeThe Code is as follows:
Create table t_team
(
Tid int primary key,
Tname varchar (100)
);
Create table t_people
(
Pid int primary key,
Pname varchiar (100 ),
Team_name varchar (100)
);
The team table and the people table are in a one-to-multiple relationship. The team tname is unique and the people pname is unique. The Chinese and foreign keys of the people table are associated with the team table tname, it is not associated with the primary key id.
(PS: Let's not say that such a design combination is unreasonable, but if it's true ..... Many tables are designed to have an id and uuid, id as the primary key, and uuid as the Association, which is similar to the above)
Query the people and team information of pname "xxg:
SELECT * FROM t_team t, t_people p WHERE t. tname = p. team_name AND p. pname = 'xxg' LIMIT 1;
Or
SELECT * FROM t_team t inner join t_people p ON t. tname = p. team_name WHERE p. pname = 'xxg' LIMIT 1;
Run the following command to query the results. However, if the data volume is large, the execution is slow and the efficiency is low.
For this connection query, the subquery is used instead. The query results are the same, but the efficiency is higher:
SELECT * FROM (SELECT * FROM t_people WHERE pname = 'xxg' LIMIT 1) p, t_team t WHERE t. tname = p. team_name LIMIT 1;
The subquery filters a large amount of data (only one record is retained) and then connects the results to the query, which greatly improves the efficiency.
(PS: In addition, the use of LIMIT 1 can also improve the query efficiency, details: http://blog.csdn.net/xiao__gui/article/details/8726272)
I tested the efficiency of two query methods through three SQL statements:
Prepare 10 thousand pieces of team data and 1 million pieces of people data.
Data creation and storage process:
Copy codeThe Code is as follows:
BEGIN
DECLARE I INT;
Start transaction;
SET I = 0;
WHILE I & lt; 10000 DO
Insert into t_team VALUES (I + 1, CONCAT ('team', I + 1 ));
SET I = I + 1;
End while;
SET I = 0;
WHILE I & lt; 1000000 DO
Insert into t_people VALUES (I + 1, CONCAT ('people', I + 1), CONCAT ('team', I % 10000 + 1 ));
SET I = I + 1;
End while;
COMMIT;
END
SQL statement execution efficiency:
Connection Query
Copy codeThe Code is as follows:
SELECT * FROM t_team t, t_people p WHERE t. tname = p. team_nameAND p. pname = 'lele20000 'LIMIT 1;
Time: 12.594 s
Connection Query
Copy codeThe Code is as follows:
SELECT * FROM t_team t inner join t_peoplep ON t. tname = p. team_name WHERE p. pname = 'lele20000 'LIMIT 1;
Time: 12.360 s
Subquery
Copy codeThe Code is as follows:
SELECT * FROM (SELECT * FROM t_people WHEREpname = 'lele20000 'LIMIT 1) p, t_team t WHERE t. tname = p. team_name LIMIT 1;
Time: 0.016 s