Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of what the MySQL external join grammar is, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this MySQL external join grammar article, let's take a look at it.

Preface

External connection can be divided into left outer connection and right outer connection.

Left outer join: contains all rows of the left table (regardless of whether there are matching rows in the table on the right), and all matching rows in the table on the right

Right outer join: contains all rows of the table on the right (regardless of whether there are matching rows in the table on the left) and all matching rows in the table on the left

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

Job record

Set up the users table first

Orders table

-- query the number of orders purchased by each user, and list uID,uName,buyNumSELECT s.uidPhoneSuNumFRom users s LEFT JOIN orders t ON s.uID=t.uID.

-- query the number of orders for each user, and list uID,uName,ordernum (number of orders) SELECT s. Uiddepartment s.uNameZhe (t.uid) as ordernumFROM orders t RIGHT JOIN users s ON s.uid=t.uidGROUP BY s. Uidlitz.-pockmarked Wang has two orders and the rest is once.

This is the end of the article on "what is the external join grammar of MySQL?" Thank you for reading! I believe you all have a certain understanding of the knowledge of "what is MySQL external join grammar". If you want to learn more, you are 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.