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

Editor to share with you what are the join queries of the mysql table? I believe that most people do not know much about it, so share this article for your reference. I hope you will learn a lot after reading this article. Let's learn about it together.

Mysql table join query are: 1, cross-join, generally used to return the Cartesian product of the join table; 2, inner join, mainly by setting join conditions to remove the cross-join of some data rows in the query results; 3, outer join, first divide the connected table into base table and reference table, and then return 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.

Internal connection

Inner join (INNER JOIN) mainly removes the cross-join of some data rows in the query results by setting the join conditions. Simply put, conditional expressions are used to eliminate some cross-connected rows of data.

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.

External connection

The query results of the inner join are all records that meet the join conditions, while the outer join will first divide the joined table into the base table and the reference table, and then return 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.

When using external join queries, 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.

What are the join queries for the mysql table above? All the contents, 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.