An INNER JOIN query joins two or more tables and returns data of multiple tables that meet the join conditions. INNER JOIN is also called SIMPLE JOIN.
Syntax
By default, the results returned by INNER JOIN meet the join conditions following the ON keyword. The INNER keyword is usually omitted. The tables that precede and follow the JOIN keyword are called the left-side table and the right-side table, respectively. The ON clause specifies the join and filter conditions for the left-side and right-side tables.
Syntax for an INNER JOIN query:
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 JOIN result is filtered by the WHERE clause and then sorted by the ORDER BY clause.
Without the ON clause, the INNER JOIN returns all data from the left and right-side tables. This result is called a Cartesian product.
If the join conditions are the same, the ON clause can be replaced by the WHERE clause to implement an INNER JOIN query. Syntax:
SELECT select_list FROM table_name1,table_name2 [ WHERE query_condition ]
Examples
Create a table and insert proper data.
obclient> CREATE TABLE tbl1(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient> CREATE TABLE tbl2(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected
obclient> 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> INSERT INTO tbl2 VALUES(1,'B2'),(3,'C2'),(6,'F2'),(9,'I2');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
A 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
A 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
A 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