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子句]
Copy after login
or
SELECT <字段名> FROM <表1>, <表2> [WHERE子句]
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 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:
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子句]
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 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)
Copy after login
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子句>
Copy after login
语法说明如下。
字段名：需要查询的字段名称。
<表1><表2>：需要左连接的表名。
LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN。
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)
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)
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!
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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
