What is the way to write Chinese and foreign connections in mysql?-Mysql Tutorial-php.cn

mysql Chinese and foreign joins can be divided into left outer joins and right outer joins. The writing methods are "SELECT field name FROM table 1 LEFT OUTER JOIN table 2 ON clause" and "SELECT field name FROM table 1 RIGHT OUTER JOIN" Table 2 ON clause".

What is the way to write Chinese and foreign connections in mysql?

(Recommended tutorial: mysql video tutorial)

Outer joins can be divided into left outer joins and right outer joins

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

Left join

Left outer join, also known as left join, uses the LEFT OUTER JOIN keyword to connect two tables, and uses the ON clause to set the connection condition.

The syntax format of left join is as follows:

SELECT <字段名> FROM <表1> LEFT OUTER JOIN <表2> <ON子句>

Copy after login

The syntax description is as follows:

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

: The name of the table that requires a left join.

LEFT OUTER JOIN: The OUTER keyword can be omitted in the left join, and only the keyword LEFT JOIN is used.

ON clause: used to set the connection condition of the left join and cannot be omitted.

In the above syntax, "Table 1" is the base table and "Table 2" is the reference table. When querying with a left join, you can query all the records in "Table 1" and the records matching the join conditions in "Table 2". If a row in "Table 1" does not have a matching row in "Table 2", then in the returned result, the field values of "Table 2" will be null (NULL).

Example 1

Before performing the left join query, we first check the data in the tb_course and tb_students_info tables. The SQL statements and running results are as follows.

mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | 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 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
| 11 | LiMing |   22 | 男   |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.00 sec)

Copy after login

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

mysql> SELECT s.name,c.course_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)

Copy after login

As you can see, the running result shows 12 records. The student named LiMing currently has no courses. Because there is no course information for the student in the corresponding tb_course table, this record is only taken out of the tb_students_info table. The corresponding value in the tb_course table is NULL.

Right join

Right outer join is also called right join, and right join is the reverse join of left join. Use the RIGHT OUTER JOIN keyword to join two tables, and use the ON clause to set the join conditions.

The syntax format of right join is as follows:

SELECT <字段名> FROM <表1> RIGHT OUTER JOIN <表2> <ON子句>

Copy after login

The syntax description is as follows:

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

: The name of the table that requires a right join.

RIGHT OUTER JOIN: The OUTER keyword can be omitted in the right join, and only the keyword RIGHT JOIN is used.

ON clause: used to set the connection condition of the right join and cannot be omitted.

Contrary to the left join, the right join uses "Table 2" as the base table and "Table 1" as the reference table. When performing a right join query, you can query all records in "Table 2" and records matching the join conditions in "Table 1". If a row in "Table 2" does not have a matching row in "Table 1", then in the returned result, the field values of "Table 1" will be null (NULL).

Example 2

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

mysql> SELECT s.name,c.course_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)

Copy after login

As you can see, the result shows 11 records. The course named HTML currently has no students. Because the corresponding tb_students_info table does not have the student's information, so this record is only taken out of the tb_course table. The corresponding value, while the value taken from the tb_students_info table is NULL.

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

When using outer join query, you must distinguish the query result, whether you need to display all the records in the left table or all the records in the right table, and then select the corresponding left join and right join.

The above is the detailed content of What is the way to write Chinese and foreign connections in mysql?. For more information, please follow other related articles on the PHP Chinese website!

Related labels：

mysql outer join

source：php.cn

Previous article：How to query data within one day in mysql Next article：How to delete database in MySQL

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn