The principle and application of MySQL connection query

Overview

One of the most powerful features of MySQL is the ability to join tables while performing data retrieval. Most single-table data queries cannot meet our needs. At this time, we need to connect one or more tables and filter out the data we need through some conditions.

Before understanding MySQL join query, let's first understand the principle of Cartesian product.

Data preparation

Still use the table data in the previous section (including the classes table and the students table):

mysql> select * from classes;
+---------+-----------+
| classid | classname |
+---------+-----------+
| 1 | Class 1, Grade 9|
| 2 | Grade 9, Class 2 |
| 3 | Grade 3, Class 3 |
| 4 | Grade 3-4 |
+---------+-----------+
4 rows in set

mysql> select * from students;
+-----------+-------------+-------+---------+
| studentid | studentname | score | classid |
+-----------+-------------+-------+---------+
| 1 | brand | 97.5 | 1 |
| 2 | helen | 96.5 | 1 |
| 3 | lyn | 96 | 1 |
| 4 | sol | 97 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 19 | lala | 51 | 0 |
+-----------+-------------+-------+---------+
9 rows in set

Cartesian Product

Cartesian product: also known as Cartesian product, assuming two sets A and B, Cartesian product represents all possible results produced by any association between the elements in set A and the elements in set B.

For example, if there are m elements in A and n elements in B, the Cartesian product of A and B produces m*n results, which is equivalent to looping through the elements in the two sets in any combination.

The implementation of Cartesian product in SQL is cross join. All join methods will first generate a temporary Cartesian product table. Cartesian product is a concept in relational algebra, which represents any combination of each row of data in two tables.

So the table above is 4 (class table) * 9 (student table) = 36 data;

Cartesian product syntax format:

 select cname1,cname2,... from tname1,tname2,...;
 or
 select cname from tname1 join tname2 [join tname...];

The legend indicates:

The actual execution results of the above two tables are as follows:

mysql> select * from classes a,students b order by a.classid,b.studentid;
+---------+-----------+-----------+-------------+-------+---------+
| classid | classname | studentid | studentname | score | classid |
+---------+-----------+-----------+-------------+-------+---------+
| 1 | Class 1, Grade 9 | 1 | brand | 97.5 | 1 |
| 1 | Class 1, Grade 9 | 2 | helen | 96.5 | 1 |
| 1 | Class 1, Grade 9 | 3 | lyn | 96 | 1 |
| 1 | Class 1, Grade 9 | 4 | sol | 97 | 1 |
| 1 | Class 1, Grade 9 | 7 | b1 | 81 | 2 |
| 1 | Class 1, Grade 9 | 8 | b2 | 82 | 2 |
| 1 | Class 1, Grade 9 | 13 | C1 | 71 | 3 |
| 1 | Class 1, Grade 9 | 14 | C2 | 72.5 | 3 |
| 1 | Class 1, Grade 9 | 19 | lala | 51 | 0 |
| 2 | Class 2, Grade 9 | 1 | brand | 97.5 | 1 |
| 2 | Class 2, Grade 9 | 2 | helen | 96.5 | 1 |
| 2 | Class 2, Grade 9 | 3 | lyn | 96 | 1 |
| 2 | Class 2, Grade 9 | 4 | sol | 97 | 1 |
| 2 | Class 2, Grade 9 | 7 | b1 | 81 | 2 |
| 2 | Class 2, Grade 9 | 8 | b2 | 82 | 2 |
| 2 | Class 2, Grade 9 | 13 | C1 | 71 | 3 |
| 2 | Class 2, Grade 9 | 14 | C2 | 72.5 | 3 |
| 2 | Class 2, Grade 9 | 19 | lala | 51 | 0 |
| 3 | Class 3, Grade 9 | 1 | brand | 97.5 | 1 |
| 3 | Class 3, Grade 9 | 2 | helen | 96.5 | 1 |
| 3 | Class 3, Grade 9 | 3 | lyn | 96 | 1 |
| 3 | Class 3, Grade 9 | 4 | sol | 97 | 1 |
| 3 | Class 3, Grade 9 | 7 | b1 | 81 | 2 |
| 3 | Class 3, Grade 9 | 8 | b2 | 82 | 2 |
| 3 | Class 3, Grade 9 | 13 | C1 | 71 | 3 |
| 3 | Class 3, Grade 9 | 14 | C2 | 72.5 | 3 |
| 3 | Class 3, Grade 9 | 19 | lala | 51 | 0 |
| 4 | Class 3-4 | 1 | brand | 97.5 | 1 |
| 4 | Class 3-4 | 2 | helen | 96.5 | 1 |
| 4 | Class 3-4 | 3 | lyn | 96 | 1 |
| 4 | Class 3-4 | 4 | sol | 97 | 1 |
| 4 | Class 3-4 | 7 | b1 | 81 | 2 |
| 4 | Class 3-4 | 8 | b2 | 82 | 2 |
| 4 | Class 3-4 | 13 | C1 | 71 | 3 |
| 4 | Class 3-4 | 14 | C2 | 72.5 | 3 |
| 4 | Class 3-4 | 19 | lala | 51 | 0 |
+---------+-----------+-----------+-------------+-------+---------+
36 rows in set

This kind of data is definitely not what we want. In practical applications, we need to add restrictions when joining tables to filter out the data we really need.

Our main connection queries are: inner connection, left (outer) connection, right (outer) connection. Let's look at them one by one.

Inner join query inner join

Syntax format:

 select cname from tname1 inner join tname2 on join condition;
 Or select cname from tname1 join tname2 on join condition;
 Or select cname from tname1,tname2 [where join condition];

Note: A join condition is added on the basis of the Cartesian product, the two tables are combined, and the records that meet the join condition are returned, that is, the intersection (shaded) part of the two tables is returned. If this join condition is not added, the result is the Cartesian product above.

mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| Class 1, Grade 9 | brand | 97.5 |
| Class 1, Grade 9 | helen | 96.5 |
| Class 1, Grade 9 | lyn | 96 |
| Class 1, Grade 9 | sol | 97 |
| Class 2, Grade 9 | b1 | 81 |
| Class 2, Grade 9 | b2 | 82 |
| Class 3, Grade 9 | C1 | 71 |
| Class 3, Grade 9 | C2 | 72.5 |
+-----------+-------------+-------+
8 rows in set

From the above data, we can see that the classid of the third and fourth grade class is 4, which is filtered out because there is no associated student; the classid of lala is 0, which cannot be associated with a specific class and is also filtered out. Only the data intersection of both tables is taken.

mysql> select a.classname,b.studentname,b.score from classes a,students b where a.classid = b.classid and a.classid=1;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| Class 1, Grade 9 | brand | 97.5 |
| Class 1, Grade 9 | helen | 96.5 |
| Class 1, Grade 9 | lyn | 96 |
| Class 1, Grade 9 | sol | 97 |
+-----------+-------------+-------+
4 rows in set

To find the grade information of students in Class 1, use the third syntax format above. This method is concise and efficient, and the Where condition is directly filtered after the result of the connection query.

Left join query left join

left join on / left outer join on, syntax format:

select cname from tname1 left join tname2 on join condition;

Note: left join is the abbreviation of left outer join, the full name is left outer join, a type of outer join. For a left (outer) join, all records in the left table (classes) will be displayed, while the right table (students) will only display records that meet the search criteria. The contents that cannot be associated with the right table are all null.

mysql> select a.classname,b.studentname,b.score from classes a left join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| Class 1, Grade 9 | brand | 97.5 |
| Class 1, Grade 9 | helen | 96.5 |
| Class 1, Grade 9 | lyn | 96 |
| Class 1, Grade 9 | sol | 97 |
| Class 2, Grade 9 | b1 | 81 |
| Class 2, Grade 9 | b2 | 82 |
| Class 3, Grade 9 | C1 | 71 |
| Class 3, Grade 9 | C2 | 72.5 |
| Class 3-4 | NULL | NULL |
+-----------+-------------+-------+
9 rows in set

From the above results, we can see that no corresponding students can be found in the third and fourth grades, so the last two fields are marked with null.

Right join query

right join on / right outer join on, syntax format:

select cname from tname1 right join tname2 on join condition;

Note: right join is the abbreviation of right outer join, and its full name is right outer join, which is a type of outer join. In contrast to the left (outer) join, in the right (outer) join, only the records in the left table (classes) that meet the search criteria will be displayed, while all the records in the right table (students) will be displayed. The places where the left table has insufficient records are all NULL.

mysql> select a.classname,b.studentname,b.score from classes a right join students b on a.classid = b.classid;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| Class 1, Grade 9 | brand | 97.5 |
| Class 1, Grade 9 | helen | 96.5 |
| Class 1, Grade 9 | lyn | 96 |
| Class 1, Grade 9 | sol | 97 |
| Class 2, Grade 9 | b1 | 81 |
| Class 2, Grade 9 | b2 | 82 |
| Class 3, Grade 9 | C1 | 71 |
| Class 3, Grade 9 | C2 | 72.5 |
| NULL | lala | 51 |
+-----------+-------------+-------+
9 rows in set

From the above results, we can see that Lala cannot find the class, so the class name field is null.

Join query + aggregate function

When using join queries, aggregate functions are often used to summarize data. For example, based on the above data, you can query the number of students in each class, the average score, and the total score of the class.

mysql> select a.classname as 'class name', count(b.studentid) as 'total number of students', sum(b.score) as 'total score', avg(b.score) as 'average score'
from classes a inner join students b on a.classid = b.classid
group by a.classid,a.classname;
+----------+--------+--------+-----------+
| Class Name| Total Number of Students| Total Score| Average Score|
+----------+--------+--------+-----------+
| Class 1, Grade 9 | 4 | 387.00 | 96.750000 |
| Class 2, Grade 9 | 2 | 163.00 | 81.500000 |
| Class 3, Grade 9 | 2 | 143.50 | 71.750000 |
+----------+--------+--------+-----------+
3 rows in set

While querying the tables here, the classes (classid, classname) are grouped and the number of students, average score, and total score of each class are output.

Additional filter conditions for connection query

After using the connection query, the data will most likely be filtered, so we can add a where condition after the connection query. For example, we can only retrieve the student information of one class based on the above results.

mysql> select a.classname,b.studentname,b.score from classes a inner join students b on a.classid = b.classid where a.classid=1;
+-----------+-------------+-------+
| classname | studentname | score |
+-----------+-------------+-------+
| Class 1, Grade 9 | brand | 97.5 |
| Class 1, Grade 9 | helen | 96.5 |
| Class 1, Grade 9 | lyn | 96 |
| Class 1, Grade 9 | sol | 97 |
+-----------+-------------+-------+
4 rows in set

As above, only students in one class are output. Similarly, you can add limit restrictions, order by sorting and other operations.

Summarize

1. The join query must include a join condition, otherwise it will become Cartesian product data. Using incorrect join conditions will also return incorrect data.

2. The SQL specification recommends that the INNER JOIN syntax be used as the first choice. However, there is no obvious performance difference between the several connection methods themselves. The performance difference is mainly determined by a combination of multiple conditions such as data structure, connection conditions, and index usage.

We should decide based on the actual business scenario, such as the above data scenario: if you are required to return classes with students, use inner join; if you must output all classes, use left join; if you must output all students, use right join.

3. Performance considerations: MySQL will process connected tables according to the association conditions during runtime. This processing may be very resource-intensive. The more tables are connected, the more severe the performance degradation. Therefore, it is necessary to analyze and remove unnecessary connections and fields that do not need to be displayed.

Previously, when my project team was optimizing old business codes, we found that as the business changed, some data no longer needed to be displayed and the corresponding connection was no longer needed. After removing it, the performance was greatly improved.

The above is the detailed content of the principle and application of MySQL connection query. For more information about MySQL connection query, please pay attention to other related articles on 123WORDPRESS.COM!