What are the connection queries for mysql tables?-Mysql Tutorial-php.cn

The connection queries for mysql tables include: 1. Cross connection, which is generally used to return the Cartesian product of the connected table; 2. Inner join, which mainly removes certain parts of the query results by setting connection conditions. Cross-connection of data rows; 3. Outer connection, first divide the connected table into a base table and a reference table, and then return records that meet and do not meet the conditions based on the base table.

What are the connection queries for mysql tables?

(Recommended tutorial: mysql video tutorial)

In a relational database, the relationship between tables is There is a relationship, so in practical applications, multi-table queries are often used. 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 join

Cross join (CROSS JOIN) is generally used to return the Cartesian product of the join table.

The syntax format of cross-connection is as follows:

SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句]

Copy after login

SELECT <字段名> FROM <表1>, <表2> [WHERE子句]

Copy after login

The syntax description is as follows:

Field name: what needs to be queried Field Name.

: The name of the table that requires cross-connection.

WHERE clause: used to set the query conditions for cross connections.

Note: When multiple tables are cross-connected, CROSS JOIN or , can be used continuously after FROM. The return results of the above two syntaxes are the same, but the first syntax is the officially recommended standard writing method.

When there is no relationship between the connected tables, we will omit the WHERE clause. At this time, the returned result is the Cartesian product of the two tables, and the number of returned results is the multiplication of the data rows of the two tables. It should be noted that if each table has 1000 rows, then the number of returned results will be 1000×1000 = 1000000 rows, and the amount of data is very huge.

Inner JOIN

Inner JOIN mainly removes certain data rows from the query results by setting connection conditions. of cross-connection. To put it simply, conditional expressions are used to eliminate certain data rows in cross-connections.

Inner join uses the INNER JOIN keyword to connect two tables, and uses the ON clause to set the connection conditions. Without join conditions, INNER JOIN and CROSS JOIN are syntactically equivalent and interchangeable.

The syntax format of inner connection is as follows:

SELECT <字段名> FROM <表1> INNER 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 inner join.

INNER JOIN: The INNER keyword can be omitted in inner joins, and only the JOIN keyword is used.

ON clause: used to set the connection conditions of the inner join.

INNER JOIN You can also use the WHERE clause to specify connection conditions, but the INNER JOIN ... ON syntax is the official standard writing method, and the WHERE clause will affect the query at some point. performance.

When connecting multiple tables, just use INNER JOIN or JOIN continuously after FROM.

Outer join

The query results of the inner join are all records that meet the connection conditions, and the outer join will 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.

Outer joins can be divided into left outer joins and right outer joins. The following describes left outer joins and right outer joins respectively based on examples.

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

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 OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

右连接的语法格式如下：

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

Copy after login

语法说明如下。

字段名：需要查询的字段名称。
<表1><表2>：需要右连接的表名。
RIGHT OUTER JOIN：右连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN。
ON 子句：用来设置右连接的连接条件，不能省略。

与左连接相反，右连接以“表2”为基表，“表1”为参考表。右连接查询时，可以查询出“表2”中的所有记录和“表1”中匹配连接条件的记录。如果“表2”的某行在“表1”中没有匹配行，那么在返回结果中，“表1”的字段值均为空值（NULL）。

例 2

在 tb_students_info 表和 tb_course 表中查询所有课程，包括没有学生的课程，SQL 语句和运行结果如下。
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

可以看到，结果显示了 11 条记录，名称为 HTML 的课程目前没有学生，因为对应的 tb_students_info 表中并没有该学生的信息，所以该条记录只取出了 tb_course 表中相应的值，而从 tb_students_info 表中取出的值为 NULL。

多个表左/右连接时，在 ON 子句后连续使用 LEFT/RIGHT OUTER JOIN 或 LEFT/RIGHT JOIN 即可。

使用外连接查询时，一定要分清需要查询的结果，是需要显示左表的全部记录还是右表的全部记录，然后选择相应的左连接和右连接。

The above is the detailed content of What are the connection queries for mysql tables?. For more information, please follow other related articles on the PHP Chinese website!

Related labels：

mysql

source：php.cn

Previous article：Online slow query accident caused by wrong index selection in MySQL Next article：How to convert string to uppercase 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