Shulou(Shulou.com)06/01 Report--

Editor to share with you the case study of mysql multi-table query, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Mysql multi-table queries generally use cross joins, inner joins and outer joins. The cross join returns the Cartesian product of the join table; the inner join combines the records in the two tables and returns the records that match the associated fields, that is, the intersection of the two tables; the outer join first divides the joined table into the base table and the reference table, and then returns records that meet and do not meet the conditions based on the base table.

In relational database, there is a relationship between tables, so multi-table queries are often used in practical applications. Multi-table query is to query two or more tables at the same time.

In MySQL, multi-table queries mainly include cross joins, inner joins and outer joins.

Cross connection

Cross joins (CROSS JOIN) are generally used to return the Cartesian product of join tables.

The syntax format for cross connections is as follows:

SELECT FROM CROSS JOIN [WHERE clause]

Or

SELECT FROM, [WHERE clause]

The syntax is as follows:

Field name: the name of the field to be queried.

The name of the table that requires a cross connection

WHERE clause: used to set the query criteria for cross-joins.

Note: when multiple tables are cross-connected, you can use CROSS JOIN or continuously after FROM. The return results of the above two grammars are the same, but the first syntax is the official recommended standard way of writing.

When there is no relationship between the joined tables, we omit the WHERE clause, and the return result is the Cartesian product of the two tables, and the number of returned results is multiplied by the data rows of the two tables. It is important to note that if each table has 1000 rows, the number of results returned is 1000 × 1000 = 1000000 rows, and the amount of data is very large.

Cross-join can query two or more tables. In order to give readers a better understanding, let's first explain the cross-join query of two tables.

Example

Query the student information table and subject information table, and get a Cartesian product.

In order to easily observe the running results after the cross-connection between the student information table and the subject table, we first query the data of the two tables, and then carry out the cross-join query.

1) query the data in the tb_students_info table, the SQL statement and the running result are as follows:

Mysql > SELECT * FROM tb_students_info +-+-+ | id | name | age | sex | height | course_id | +-+-+ | 1 | Dany | 25 | male | 160 | 1 | 2 | Green | 23 | male | 3 | Henry | 23 | female | 185 | 1 | 4 | Jane | 22 | male | 162 | 3 | 5 | Jim | 24 | female | 175 | 2 | 6 | John | 21 | female | 172 | 4 | 7 | Lily | 22 | | male | 165,4 | 8 | Susan | 23 | male | 170,5 | 9 | Thomas | 22 | female | 178,5 | 10 | Tom | 23 | female | 165,165 | 5 | +-+-+ 10 rows in set (0.00 sec)

2) query the data in the tb_course table, the SQL statement and the running result are as follows:

Mysql > SELECT * FROM tb_course;+----+-+ | id | course_name | +-+ | 1 | Java | | 2 | MySQL | | 3 | Python | 4 | Go | | 5 | C++ | +-+-+ 5 rows in set (0.00 sec)

3) use CROSS JOIN to query the Cartesian product of the two tables, the SQL statement and the running result are as follows:

Mysql > SELECT * FROM tb_course CROSS JOIN tb_students_info +-- +-- id | course_name | id | name | age | sex | height | course_id | +-- -- + | 1 | Java | 1 | Dany | 25 | male | 160 | 1 | 2 | MySQL | 1 | Dany | 25 | male | 160 | 1 | 3 | Python | 1 | Dany | 25 | male | 160 | 1 | 1 | 4 | Go | 1 | Dany | 25 | male | 16 0 | 1 | 5 | C++ | 1 | Dany | 25 | male | 1 | Java | 2 | Green | 23 | male | 2 | MySQL | 2 | Green | 23 | male | 3 | Python | 2 | Green | 23 | male | | | 158 | 2 | 4 | Go | 2 | Green | 23 | male | 158 | 2 | 5 | C++ | 2 | Green | 23 | male | 2 | 2 | Java | 3 | Henry | 23 | female | 185 | 1 | 2 | MySQL | 3 | Henry | 23 | female | 185 | 1 | 3 | Python | | | 3 | Henry | 23 | female | 185 | 1 | 4 | Go | 3 | Henry | 23 | female | 185 | 1 | 5 | C++ | 3 | Henry | 23 | female | 185 | 1 | 1 | Java | 4 | Jane | 22 | male | 162 | 3 | MySQL | 4 | Jane | 22 | male | | | 162 | 3 | 3 | Python | 4 | Jane | 22 | male | 3 | 4 | Go | 4 | Jane | 22 | male | 162 | 3 | 5 | C++ | 4 | Jane | 22 | male | 3 | 1 | Java | 5 | Jim | 24 | female | 175 | 2 | | 2 | | | MySQL | 5 | Jim | 24 | female | 175 | 2 | 3 | Python | 5 | Jim | 24 | female | 2 | 4 | Go | 5 | Jim | 24 | female | 2 | 5 | C++ | 5 | Jim | 24 | female | 2 | 1 | Java | 6 | John | | 21 | MySQL | 6 | John | 21 | female | 3 | Python | 6 | John | 21 | female | 4 | 4 | Go | 6 | John | 21 | female | 4 | 4 | 5 | C++ | 6 | John | 21 | female | 172 | 21 | | 4 | | 1 | Java | 7 | Lily | 22 | male | 2 | MySQL | 7 | Lily | 22 | male | 3 | Python | 7 | Lily | 22 | male | 4 | 4 | Go | 7 | Lily | 22 | male | 165,4 | 4 | 5 | C++ | 7 | Lily | 22 | male | 165,4 | 1 | Java | 8 | Susan | 23 | male | 170 | 5 | 2 | MySQL | 8 | Susan | 23 | male | 170 | 5 | 3 | Python | 8 | Susan | 23 | male | 170 | 5 | 4 | Go | 8 | Susan | 23 | male | 23 | male | 170 | | 5 | 5 | C++ | 8 | Susan | 23 | male | 170 | 5 | 1 | Java | 9 | Thomas | 22 | female | 178 | 5 | 2 | MySQL | 9 | Thomas | 22 | female | 5 | 3 | Python | 9 | Thomas | 22 | female | 5 | 4 | Go | 9 | Thomas | 22 | female | 178 | 5 | 5 | C++ | 9 | Thomas | 22 | female | 5 | 1 | Java | 10 | Tom | 23 | female | 2 | MySQL | 10 | Tom | 23 | female | 5 | 3 | Python | 10 | Tom | 23 | female | | 5 | | 4 | Go | 10 | Tom | 23 | female | 165,5 | 5 | C++ | 10 | Tom | 23 | female | 165th | 5 | +-- -+ 50 rows in set (0.00 sec)

As can be seen from the running results, 50 records are returned after the tb_course and tb_students_info tables are cross-joined. As you can imagine, when there is more data in the table, the run result will be very long, and the run result will not make much sense. Therefore, this method of multi-table query through cross-join is not commonly used, and we should try our best to avoid this kind of query.

Cartesian product

Cartesian product (Cartesian product) is the product of two sets X and Y.

For example, there are two sets An and B, whose values are as follows:

A = {1pyrine 2} B = {3pyrine 4pyr5}

The result sets of sets A × B and B × An are expressed as follows:

A × B = {(1pr 3), (1pr 4), (1je 5), (2pr 3), (2je 4), (2pr 5)}; B × A = {(3pr 1), (3pr 2), (4je 1), (4je 2), (5p 1), (5p 2)}

The above results of A × B and B × An are called the Cartesian product of two sets.

And, from the above results, we can see:

The multiplication of two sets does not satisfy the exchange rate, that is, A × B ≠ B × A.

The Cartesian product of A set and B set is the number of elements of A set × the number of elements of B set.

The algorithm followed by multi-table query is the Cartesian product mentioned above, and the join between tables can be regarded as multiplication. In practical application, the use of Cartesian product should be avoided, because there are a lot of unreasonable data in Cartesian product, which can easily lead to repetition and confusion of query results.

Internal connection

Inner join (INNER JOIN) combines records in two tables and returns records that match the associated fields by setting join conditions, that is, the intersection (shadow) part of the two tables.

The inner join joins two tables using the INNER JOIN keyword and sets the join condition using the ON clause. If there is no join condition, INNER JOIN and CROSS JOIN are syntactically equivalent and are interchangeable.

The syntax format of the inner connection is as follows:

SELECT FROM INNER JOIN [ON clause]

The syntax is explained as follows.

Field name: the name of the field to be queried.

The name of the table that requires an inner connection

INNER JOIN: the INNER keyword can be omitted in the inner connection and only the keyword JOIN can be used.

ON clause: used to set the connection condition of an inner connection.

INNER JOIN can also use the WHERE clause to specify the connection condition, but INNER JOIN. ON syntax is the official standard, and WHERE clauses can affect query performance at some point.

When multiple tables are joined, you can use INNER JOIN or JOIN continuously after FROM.

Example:

Between the tb_students_info table and the tb_course table, use the inner join to query the student name and the corresponding course name. The SQL statement and the running result are as follows.

Mysql > SELECT s.name _ FROM tb_students_info s INNER JOIN tb_course c-> ON s.course_id = c.id +-+ | name | course_name | +-+-+ | Dany | Java | | Green | MySQL | | Henry | Java | | Jane | Python | | Jim | MySQL | | John | Go | | Lily | Go | Susan | C++ | Thomas | C++ | | | Tom | C++ | +-+-+ 10 rows in set (0.00 sec) |

In the query statement here, the relationship between the two tables is specified by INNER JOIN, and the conditions of the join are given using the ON clause.

Note: when querying multiple tables, specify which table the field is derived from after the SELECT statement. Therefore, when querying multiple tables, the SELECT statement is followed by the table name. List. In addition, if the table name is very long, you can set an alias for the table so that you can write the alias of the table directly after the SELECT statement. List.

External connection

The outer join first divides the joined table into the base table and the reference table, and then returns records that meet and do not meet the conditions based on the base table.

The outer connection can be divided into left outer connection and right outer connection. The left outer connection and right outer connection are introduced respectively according to the examples below.

Left connection

The left outer join, also known as the left join, joins two tables using the LEFT OUTER JOIN keyword and sets the join condition using the ON clause.

The syntax format of the left join is as follows:

SELECT FROM LEFT OUTER JOIN

The syntax is explained as follows.

Field name: the name of the field to be queried.

The table name that needs to be connected to the left

LEFT OUTER JOIN: the OUTER keyword can be omitted from the left connection and only the keyword LEFT JOIN can be used.

ON clause: used to set the connection condition of the left connection, which cannot be omitted.

In the above syntax, "Table 1" is the base table and "Table 2" is the reference table. When you join the query on the left, you can query all the records in Table 1 and the records in Table 2 that match the join conditions. If a row of Table 1 does not match in Table 2, the field values of Table 2 are all NULL in the returned results.

Example 1

Before doing the left join query, let's look at the data in the tb_course and tb_students_info tables. The SQL statement and the run result are as follows.

Mysql > SELECT * FROM tb_course +-Java | 2 | MySQL | 3 | Python | 4 | Go | 5 | C++ | 6 | HTML | +-+ 6 rows in set (0.00 sec) mysql > SELECT * FROM tb_students_info +-+-+ | id | name | age | sex | height | course_id | +-+-+ | 1 | Dany | 25 | male | 160 | 1 | 2 | Green | 23 | male | 3 | Henry | 23 | female | 185 | 1 | 4 | Jane | 22 | male | 162 | 3 | 5 | Jim | 24 | female | 175 | 2 | 6 | John | 21 | female | 172 | 4 | 7 | Lily | 22 | | male | 165,4 | 8 | Susan | 23 | male | 170,5 | 9 | Thomas | 22 | female | 178,5 | 10 | Tom | 23 | female | 165th | | 11 | LiMing | 22 | male | 180 | 7 | + -+-+ 11 rows in set (0.00 sec)

Query the tb_students_info table and the tb_course table for all student names and corresponding course names, including students without courses. The SQL statement and run results are as follows.

Mysql > SELECT s.name _ FROM tb_students_info s LEFT OUTER JOIN tb_course c-> ON s.`course _ id` = c.`id` +-+ | name | course_name | +-+-+ | Dany | Java | | Henry | Java | | NULL | Java | | Green | MySQL | | Jim | MySQL | | Jane | Python | | John | Go | | Lily | Go | Susan | C++ | | | Thomas | C++ | | Tom | C++ | | LiMing | NULL | +-+-+ 12 rows in set (0.00 sec) |

As you can see, the running result shows 12 records. The student whose name is LiMing does not currently have a course, because there is no course information for the student in the corresponding tb_course table, so the record only takes out the corresponding value in the tb_students_info table, while the value taken out from the tb_course table is NULL.

Right connection

The right outer connection is also known as the right connection, and the right connection is the reverse connection of the left connection. Join two tables using the RIGHT OUTER JOIN keyword and set the join condition using the ON clause.

The syntax format of the right connection is as follows:

SELECT FROM RIGHT OUTER JOIN

The syntax is explained as follows.

Field name: the name of the field to be queried.

The table name that needs to be connected to the right

RIGHT OUTER JOIN: the OUTER keyword can be omitted from the right connection and only the keyword RIGHT JOIN can be used.

ON clause: used to set the connection condition of the right connection, which cannot be omitted.

In contrast to the left join, the right join takes Table 2 as the base table and Table 1 as the reference table. When you join the query on the right, you can query all the records in Table 2 and the records in Table 1 that match the join conditions. If a row of Table 2 does not match in Table 1, the field values of Table 1 are all NULL in the returned results.

Example 2

Query all courses in the tb_students_info table and the tb_course table, including those without students, the SQL statement and the run results are as follows.

Mysql > SELECT s.name _ FROM tb_students_info s RIGHT OUTER JOIN tb_course c-> ON s.`course _ id` = c.`id` +-+ | name | course_name | +-+-+ | Dany | Java | | Green | MySQL | | Henry | Java | | Jane | Python | | Jim | MySQL | | John | Go | | Lily | Go | Susan | C++ | Thomas | C++ | | | Tom | C++ | | NULL | HTML | +-+-+ 11 rows in set (0.00 sec) |

As you can see, the result shows 11 records, and there are currently no students for the course named HTML, because there is no information about the student in the corresponding tb_students_info table, so the record only fetches the corresponding value from the tb_course table, while the value extracted from the tb_students_info table is NULL.

When multiple tables are joined left / right, you can use LEFT/RIGHT OUTER JOIN or LEFT/RIGHT JOIN continuously after the ON clause.

Note: when using external join query, be sure to distinguish the results of the query, whether you need to display all the records of the left table or all the records of the right table, and then select the corresponding left join and right join.

The above is all the contents of the case study of mysql multi-table query, thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.