How to query two tables in mysql

Methods to query two tables: 1. Use the SELECT statement and the "CROSS JOIN" keyword to perform cross-join queries; 2. Use the SELECT statement and the "INNER JOIN" keyword to perform inner join queries; 3. Use SELECT statement and "OUTER JOIN" keyword perform outer join query.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In a relational database, tables are related, 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.

MySQL Cross Join

Cross join (CROSS JOIN) is generally used to return the Cartesian product of the joined 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-joined, just use CROSS JOIN or , 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.

  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.

  MySQL Inner Connection

  Inner JOIN mainly removes certain data from query results by setting connection conditions. Cross-connection of rows. 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子句]

  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 can also use the WHERE clause to specify the connection conditions, but the INNER JOIN ... ON syntax is the official standard writing method, and the WHERE clause will sometimes Affects query performance.

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

  Inner joins can query two or more tables. In order to give everyone a better understanding, we will only explain the connection query between two tables for the time being.

  Example:

  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)

  In the query statement here, the relationship between the two tables is specified through INNER JOIN, and the conditions for the connection are given using the ON clause.

  Note: When querying multiple tables, you must specify which table the fields come from after the SELECT statement. Therefore, when querying multiple tables, the writing method after the SELECT statement is table name.column name. In addition, if the table name is very long, you can also set an alias for the table, so that you can write the table's alias and column name directly after the SELECT statement.

  MySQL 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 tables into are the base table and the reference table, and then use the base table as a basis to return records that meet and do not meet the conditions.

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

  左连接

  左外连接又称为左连接，使用 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）。

  示例：

  在进行左连接查询之前，我们先查看 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）。

  示例：

  在 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 即可。

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

  【相关推荐：mysql视频教程】

The above is the detailed content of How to query two tables in mysql. For more information, please follow other related articles on the PHP Chinese website!