What are the internal and external queries of mysql?-Mysql Tutorial-php.cn

Mysql's inner query uses conditional expressions to eliminate certain data rows in cross-connections by setting connection conditions. The query results are all records that meet the connection conditions; while the outer query will first connect the The table is divided into a base table and a reference table, and records that meet or do not meet the conditions are returned based on the base table.

What are the internal and external queries of mysql?

(Recommended tutorial: mysql video tutorial)

MySQL INNER JOIN: inner join Query

Inner JOIN mainly removes cross-connections of certain data rows in the query results by setting connection conditions. 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 join 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.

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

Between the tb_students_info table and the tb_course table, use inner joins to query student names and corresponding course names. The SQL statement and running results are as follows.

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 LEFT/RIGHT JOIN: Outer join query

The query results of the inner join are all records that meet the connection conditions, and the outer join will First, the connected table is divided into a base table and a reference table, and then the records that meet and do not meet the conditions are returned 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 child Sentence 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 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.
<表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 即可。

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