What is the method of inner join query in MySQL

Inner connection is also called a connection and is the earliest type of connection. Also known as a normal join or a natural join, an inner join deletes all rows from the result table that do not match rows in other joined tables, so information may be lost in the inner join.

Use the keyword INNER JOIN in the MySQL FROM clause to connect two tables, and use the ON clause to set the connection conditions. Syntactically, INNER JOIN and CROSS JOIN can be interchanged without any conditions.

The syntax format is as follows

SELECT 
FROM  INNER JOIN  [ ON子句]

The syntax description is as follows

: The column name to be retrieved.

: The table names of the two tables for inner connection.

After the FROM clause, you can omit the INNER keyword and use the JOIN keyword directly, because inner joins are the system's default table connection method. After using inner joins, the conditions for joining tables can be set through the ON clause in the FROM clause.

INNER JOIN or JOIN can be used continuously between multiple tables in the FROM clause, so that inner joins of multiple tables can be realized at the same time.

Both the table tb_students_info and the table tb_departments contain the field dept_id of the same data type, and an inner join query is used between the two tables.

The input SQL statement and execution results are as follows

mysql> SELECT id,name,age,dept_name
   -> FROM tb_students_info,tb_departments
   -> WHERE tb_students_info.dept_id=tb_departments.dept_id;
+----+--------+------+-----------+
| id | name   | age  | dept_name |
+----+--------+------+-----------+
|  1 | Dany   |   25 | Computer  |
|  2 | Green  |   23 | Chinese   |
|  3 | Henry  |   23 | Math      |
|  4 | Jane   |   22 | Computer  |
|  5 | Jim    |   24 | Computer  |
|  6 | John   |   21 | Math      |
|  7 | Lily   |   22 | Computer  |
|  8 | Susan  |   23 | Economy   |
|  9 | Thomas |   22 | Chinese   |
| 10 | Tom    |   23 | Economy   |
+----+--------+------+-----------+
10 rows in set (0.00 sec)

Here, the biggest difference between the SELECT statement and the previous introduction is: the columns specified after SELECT belong to two different tables, id , name, and age are in the table tb_students_info, and dept_name is in the table tb_departments. At the same time, the FROM clause lists the two tables tb_students_info and tb_departments. The WHERE clause is used as a filter condition here, indicating that only when the dept_id field values ​​in the two tables are equal, the conditions for the join query are met.

You can see from the returned results that the displayed records are new records composed of different column values ​​in the two tables.

Tip: Because the tb_students_info table and the tb_departments table have the same field dept_id, when comparing, the table name needs to be fully qualified (the format is "table name.column name"). If only dept_id is given, MySQL will not know which one is referred to and will return an error message.

Between the tb_students_info table and the tb_departments table, use INNER JOIN syntax for inner join query. The input SQL statement and execution results are as follows.

mysql> SELECT id,name,age,dept_name
   -> FROM tb_students_info INNER JOIN tb_departments
   -> WHERE tb_students_info.dept_id=tb_departments.dept_id;
+----+--------+------+-----------+
| id | name   | age  | dept_name |
+----+--------+------+-----------+
|  1 | Dany   |   25 | Computer  |
|  2 | Green  |   23 | Chinese   |
|  3 | Henry  |   23 | Math      |
|  4 | Jane   |   22 | Computer  |
|  5 | Jim    |   24 | Computer  |
|  6 | John   |   21 | Math      |
|  7 | Lily   |   22 | Computer  |
|  8 | Susan  |   23 | Economy   |
|  9 | Thomas |   22 | Chinese   |
| 10 | Tom    |   23 | Economy   |
+----+--------+------+-----------+
10 rows in set (0.00 sec)

INNER JOIN is used to specify the relationship between two tables in the query statement.. When using this syntax, the connection conditions are given using the ON clause instead of WHERE. ON and the conditions specified after WHERE are the same.

The above is the detailed content of What is the method of inner join query in MySQL. For more information, please follow other related articles on the PHP Chinese website!