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

This article mainly introduces the meaning of mysql internal and external query, the article is very detailed, has a certain reference value, interested friends must read it!

The inner query of mysql uses conditional expressions to eliminate some cross-connected data rows by setting join conditions, and the query results are records that meet the join conditions, while the outer query will first divide the joined tables into base tables and reference tables, and then return records that meet or do not meet the conditions based on the base table.

MySQL INNER JOIN: internal join query

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 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.

Internal joins can query two or more tables. In order to give you a better understanding, we will only explain the join query of two tables for the time being.

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.

MySQL LEFT/RIGHT JOIN: external join query

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.

The above are all the contents of the internal and external query of mysql. Thank you for your reading! Hope to share the content to help you, more related 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.