INNER JOIN queries|V4.1.0| docs|Distributed Database

INNER JOIN queries

Last Updated：2023-08-01 06:02:28 Updated

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 respectively called the left-side table and the right-side table. 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 data into the table.

obclient [SYS]> CREATE TABLE tbl1(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected

obclient [SYS]> CREATE TABLE tbl2(id NUMBER NOT NULL PRIMARY KEY, name VARCHAR(50));
Query OK, 0 rows affected

obclient [SYS]> 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 [SYS]> INSERT INTO tbl2 VALUES(1,'B2'),(3,'C2'),(6,'F2'),(9,'I2');
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

Examples of queries with join conditions

Create a table, and insert proper data into the table. Query the data with join conditions.

Query data of the id and name columns whose id column values are the same in the tbl1 and tbl2 tables.

obclient [SYS]> 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

Use an INNER JOIN query to obtain data of the id and name columns whose id column values are the same in the tbl1 and tbl2 tables.

obclient [SYS]> 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

Use a WHERE condition to obtain data of the id and name columns whose id column values are the same in the tbl1 and tbl2 tables.

obclient [SYS]> 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

Examples of queries without join conditions

Based on the tables and data created in the preceding example, use a query without join conditions to obtain data of the id and name columns whose id column values are the same in the tbl1 and tbl2 tables.

obclient [SYS]> 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

Queries with combined conditions

Based on the tables and data created in the preceding examples, query data with combined conditions.

Use an inner join with an AND condition to query data.

obclient [SYS]> 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

Use an inner join with a WHERE condition to query data.

obclient [SYS]> 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

Use a WHERE condition with an AND condition to query data.

obclient [SYS]> 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

Use an inner join with an ORDER BY clause to query data.

obclient [SYS]> 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