An inner join (INNER JOIN) combines two or more tables to return data that meets the join conditions. INNER JOIN is also called a simple join.
Syntax of INNER JOIN
By default, the result returned by JOIN satisfies the join conditions specified after ON. The keyword INNER is often omitted. The tables before and after JOIN are referred to as the left table and the right table, respectively. The condition in ON describes the join conditions and filter conditions for the left and right tables.
The syntax of 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 both the WHERE and ORDER BY clauses are used, the query result is first filtered by the conditions after WHERE, and then sorted by the columns specified after ORDER BY.
Without an ON clause, INNER JOIN returns all data from the left and right tables, which is called the Cartesian product.
Under the same join conditions, the ON clause can also be replaced by a WHERE clause to implement an inner join query. The syntax is as follows:
SELECT select_list FROM table_name1,table_name2 [ WHERE query_condition ]
Examples
Create sample tables and insert appropriate data.
obclient [info]> CREATE TABLE tbl1(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient [info]> CREATE TABLE tbl2(id INT NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient [info]> 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
obclient [info]> 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 conditions
obclient> 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
obclient> 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
obclient> 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 conditions
obclient> 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
obclient> 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
obclient> 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
obclient> 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
obclient> 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