What is generally used for mysql multi-table queries?

Mysql multi-table queries generally use cross joins, inner joins and outer joins. Cross-join returns the Cartesian product of the connected tables; inner join combines records in two tables and returns records with matching associated fields, that is, returns the intersection of the two tables; outer join first divides the connected tables into base tables and Reference table, and then return records that meet and do not meet the conditions based on the base table.

(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子句]

or

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

The syntax description is as follows:

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

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

  Cross connection can query two or more tables. In order to give readers a better understanding, the following will first explain the cross connection query of two tables.

  Example

  Query the student information table and subject information table, and get a Cartesian product.

  In order to facilitate the observation of the running results after the cross connection between the student information table and the subject table, we first query the data of these two tables separately, and then perform the cross connection query.

  1) Query the data in the tb_students_info table. The SQL statement and running results are as follows:

  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 |
  +----+--------+------+------+--------+-----------+
  10 rows in set (0.00 sec)

  2) Query the data in the tb_course table. The SQL statement and running results are as follows:

  mysql> SELECT * FROM tb_course;
  +----+-------------+
  | id | course_name |
  +----+-------------+
  |  1 | Java        |
  |  2 | MySQL       |
  |  3 | Python      |
  |  4 | Go          |
  |  5 | C++         |
  +----+-------------+
  5 rows in set (0.00 sec)

  3) Use CROSS JOIN to query the Cartesian product of the two tables. The SQL statement and running results are as follows:

  mysql> SELECT * FROM tb_course CROSS JOIN tb_students_info;
  +----+-------------+----+--------+------+------+--------+-----------+
  | id | course_name | id | name   | age  | sex  | height | course_id |
  +----+-------------+----+--------+------+------+--------+-----------+
  |  1 | Java        |  1 | Dany   |   25 | 男   |    160 |         1 |
  |  2 | MySQL       |  1 | Dany   |   25 | 男   |    160 |         1 |
  |  3 | Python      |  1 | Dany   |   25 | 男   |    160 |         1 |
  |  4 | Go          |  1 | Dany   |   25 | 男   |    160 |         1 |
  |  5 | C++         |  1 | Dany   |   25 | 男   |    160 |         1 |
  |  1 | Java        |  2 | Green  |   23 | 男   |    158 |         2 |
  |  2 | MySQL       |  2 | Green  |   23 | 男   |    158 |         2 |
  |  3 | Python      |  2 | Green  |   23 | 男   |    158 |         2 |
  |  4 | Go          |  2 | Green  |   23 | 男   |    158 |         2 |
  |  5 | C++         |  2 | Green  |   23 | 男   |    158 |         2 |
  |  1 | Java        |  3 | Henry  |   23 | 女   |    185 |         1 |
  |  2 | MySQL       |  3 | Henry  |   23 | 女   |    185 |         1 |
  |  3 | Python      |  3 | Henry  |   23 | 女   |    185 |         1 |
  |  4 | Go          |  3 | Henry  |   23 | 女   |    185 |         1 |
  |  5 | C++         |  3 | Henry  |   23 | 女   |    185 |         1 |
  |  1 | Java        |  4 | Jane   |   22 | 男   |    162 |         3 |
  |  2 | MySQL       |  4 | Jane   |   22 | 男   |    162 |         3 |
  |  3 | Python      |  4 | Jane   |   22 | 男   |    162 |         3 |
  |  4 | Go          |  4 | Jane   |   22 | 男   |    162 |         3 |
  |  5 | C++         |  4 | Jane   |   22 | 男   |    162 |         3 |
  |  1 | Java        |  5 | Jim    |   24 | 女   |    175 |         2 |
  |  2 | MySQL       |  5 | Jim    |   24 | 女   |    175 |         2 |
  |  3 | Python      |  5 | Jim    |   24 | 女   |    175 |         2 |
  |  4 | Go          |  5 | Jim    |   24 | 女   |    175 |         2 |
  |  5 | C++         |  5 | Jim    |   24 | 女   |    175 |         2 |
  |  1 | Java        |  6 | John   |   21 | 女   |    172 |         4 |
  |  2 | MySQL       |  6 | John   |   21 | 女   |    172 |         4 |
  |  3 | Python      |  6 | John   |   21 | 女   |    172 |         4 |
  |  4 | Go          |  6 | John   |   21 | 女   |    172 |         4 |
  |  5 | C++         |  6 | John   |   21 | 女   |    172 |         4 |
  |  1 | Java        |  7 | Lily   |   22 | 男   |    165 |         4 |
  |  2 | MySQL       |  7 | Lily   |   22 | 男   |    165 |         4 |
  |  3 | Python      |  7 | Lily   |   22 | 男   |    165 |         4 |
  |  4 | Go          |  7 | Lily   |   22 | 男   |    165 |         4 |
  |  5 | C++         |  7 | Lily   |   22 | 男   |    165 |         4 |
  |  1 | Java        |  8 | Susan  |   23 | 男   |    170 |         5 |
  |  2 | MySQL       |  8 | Susan  |   23 | 男   |    170 |         5 |
  |  3 | Python      |  8 | Susan  |   23 | 男   |    170 |         5 |
  |  4 | Go          |  8 | Susan  |   23 | 男   |    170 |         5 |
  |  5 | C++         |  8 | Susan  |   23 | 男   |    170 |         5 |
  |  1 | Java        |  9 | Thomas |   22 | 女   |    178 |         5 |
  |  2 | MySQL       |  9 | Thomas |   22 | 女   |    178 |         5 |
  |  3 | Python      |  9 | Thomas |   22 | 女   |    178 |         5 |
  |  4 | Go          |  9 | Thomas |   22 | 女   |    178 |         5 |
  |  5 | C++         |  9 | Thomas |   22 | 女   |    178 |         5 |
  |  1 | Java        | 10 | Tom    |   23 | 女   |    165 |         5 |
  |  2 | MySQL       | 10 | Tom    |   23 | 女   |    165 |         5 |
  |  3 | Python      | 10 | Tom    |   23 | 女   |    165 |         5 |
  |  4 | Go          | 10 | Tom    |   23 | 女   |    165 |         5 |
  |  5 | C++         | 10 | Tom    |   23 | 女   |    165 |         5 |
  +----+-------------+----+--------+------+------+--------+-----------+
  50 rows in set (0.00 sec)

  It can be seen from the running results that after the cross-join query of the tb_course and tb_students_info tables, 50 items were returned Record. As you can imagine, when there is a lot of data in the table, the running results obtained will be very long, and the running results obtained are not very meaningful. Therefore, this method of multi-table query through cross connection is not commonly used, and we should try to avoid this kind of query.

  Cartesian product

  The Cartesian product refers to the product of two sets X and Y.

  For example, there are two sets A and B, and their values ​​are as follows:

  A = {1,2}
  B = {3,4,5}

  The result sets of sets A×B and B×A are respectively expressed as:

  A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
  B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };

  The above results of A×B and B×A are called the Cartesian product of the two sets.

  And, from the above results, we can see that:

  • The multiplication of two sets does not satisfy the exchange rate, that is, A×B≠B×A.

  • The Cartesian product of set A and set B is the number of elements of set A × the number of elements of set B.

  The algorithm followed by multi-table queries is the Cartesian product mentioned above. The connection between tables can be regarded as a multiplication operation. In practical applications, the use of Cartesian product should be avoided because there is a large amount of unreasonable data in the Cartesian product. Simply put, it can easily lead to duplicate and confusing query results.

  Inner JOIN

  Inner JOIN combines records in two tables and returns related fields by setting connection conditions. Matching records, that is, the intersection (shaded) part of the two tables is returned.

  

  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子句]

  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 也可以使用 WHERE 子句指定连接条件，但是 INNER JOIN ... ON 语法是官方的标准写法，而且 WHERE 子句在某些时候会影响查询的性能。

  多个表内连接时，在 FROM 后连续使用 INNER JOIN 或 JOIN 即可。

  示例：
  

  在 tb_students_info 表和 tb_course 表之间，使用内连接查询学生姓名和相对应的课程名称，SQL 语句和运行结果如下。

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

  在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定，连接的条件使用 ON 子句给出。

  注意：当对多个表进行查询时，要在 SELECT 语句后面指定字段是来源于哪一张表。因此，在多表查询时，SELECT 语句后面的写法是表名.列名。另外，如果表名非常长的话，也可以给表设置别名，这样就可以直接在 SELECT 语句后面写上表的别名.列名。

  外连接

  外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

  外连接可以分为左外连接和右外连接，下面根据实例分别介绍左外连接和右外连接。

  左连接

  左外连接又称为左连接，使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

  左连接的语法格式如下：

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

  语法说明如下。

  • 字段名：需要查询的字段名称。

  • ：需要左连接的表名。

  • LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。

  • ON 子句：用来设置左连接的连接条件，不能省略。

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

  示例1

  在进行左连接查询之前，我们先查看 tb_course 和 tb_students_info 两张表中的数据。SQL 语句和运行结果如下。

  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)

  在 tb_students_info 表和 tb_course 表中查询所有学生姓名和相对应的课程名称，包括没有课程的学生，SQL 语句和运行结果如下。

  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)

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

  右连接

  右外连接又称为右连接，右连接是左连接的反向连接。使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件。

  右连接的语法格式如下：

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

  语法说明如下。

  • 字段名：需要查询的字段名称。

  • ：需要右连接的表名。

  • 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)

  可以看到，结果显示了 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 is generally used for mysql multi-table queries?. For more information, please follow other related articles on the PHP Chinese website!