INNER JOIN
An inner join is a join between two or more tables that returns data from multiple tables that meet the join condition. An inner join is also known as a simple join.
INNER JOIN syntax
By default, JOIN returns the result that meets the join condition specified after ON. The keyword INNER is usually omitted. The table before JOIN is called the left table, and the table after JOIN is called the right table. The condition in ON describes the join condition and filter condition between the left and right tables.
The SQL syntax for an inner join is as follows:
SELECT select_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]
If you use both the WHERE and ORDER BY clauses, the SQL query returns the result of the JOIN operation after filtering the result with the query condition specified after WHERE and sorting the result with the column specified after ORDER BY.
If you do not specify the ON clause, INNER JOIN returns all data from the left and right tables, which is called the Cartesian product.
The ON clause can be replaced with the WHERE clause to implement an inner join under the same join conditions. The syntax is as follows:
SELECT select_list FROM table_name1，table_name2 [ WHERE query_condition ]
Examples
Create sample tables and insert appropriate data.
CREATE TABLE tbl1(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
CREATE TABLE tbl2(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
INSERT INTO tbl1 VALUES(1,'A1'),(2,'B1'),(4,'D1'),(6,'F1'),(8,'H1'),(10,'J1');
Query OK, 6 rows affected
Records: 6 Duplicates: 0 Warnings: 0
INSERT INTO tbl2 VALUES(1,'B2'),(3,'C2'),(6,'F2'),(9,'I2');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
Query with join condition
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 JOIN tbl2 ON tbl1.id=tbl2.id;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| 6 | F1 | 6 | F2 |
+----+------+----+------+
2 rows in set
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| 6 | F1 | 6 | F2 |
+----+------+----+------+
2 rows in set
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1,tbl2 WHERE tbl1.id=tbl2.id;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| 6 | F1 | 6 | F2 |
+----+------+----+------+
2 rows in set
Query without join condition
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| 1 | A1 | 3 | C2 |
| 1 | A1 | 6 | F2 |
| 1 | A1 | 9 | I2 |
| 2 | B1 | 1 | B2 |
| 2 | B1 | 3 | C2 |
| 2 | B1 | 6 | F2 |
| 2 | B1 | 9 | I2 |
| 4 | D1 | 1 | B2 |
| 4 | D1 | 3 | C2 |
| 4 | D1 | 6 | F2 |
| 4 | D1 | 9 | I2 |
| 6 | F1 | 1 | B2 |
| 6 | F1 | 3 | C2 |
| 6 | F1 | 6 | F2 |
| 6 | F1 | 9 | I2 |
| 8 | H1 | 1 | B2 |
| 8 | H1 | 3 | C2 |
| 8 | H1 | 6 | F2 |
| 8 | H1 | 9 | I2 |
| 10 | J1 | 1 | B2 |
| 10 | J1 | 3 | C2 |
| 10 | J1 | 6 | F2 |
| 10 | J1 | 9 | I2 |
+----+------+----+------+
24 rows in set
Query with combined conditions
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id AND tbl1.id=6;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 6 | F1 | 6 | F2 |
+----+------+----+------+
1 row in set
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id WHERE tbl1.id=6;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 6 | F1 | 6 | F2 |
+----+------+----+------+
1 row in set
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1,tbl2 WHERE tbl1.id=tbl2.id AND tbl1.id=6;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 6 | F1 | 6 | F2 |
+----+------+----+------+
1 row in set
SELECT tbl1.id, tbl1.name, tbl2.id, tbl2.name FROM tbl1 INNER JOIN tbl2 ON tbl1.id=tbl2.id ORDER BY tbl1.id DESC;
+----+------+----+------+
| id | name | id | name |
+----+------+----+------+
| 6 | F1 | 6 | F2 |
| 1 | A1 | 1 | B2 |
+----+------+----+------+
2 rows in set