INNER JOIN queries

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 an INNER JOIN query 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.

The syntax of an INNER JOIN query 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 JOIN result is filtered by the WHERE clause and then sorted by the ORDER BY clause.

Without the ON clause, INNER JOIN returns all data from the left-side 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. The syntax is as follows:

 SELECT select_list FROM table_name1, table_name2 [ WHERE query_condition ]

Examples

Create a table and insert proper 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

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
  

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
  

References

• FULL JOIN queries

• LEFT JOIN queries

• RIGHT JOIN queries