Oracle Inner join clause (multi-table connection)

In this tutorial, you will learn the Oracle INNER JOIN clause to retrieve rows from a table that have matching rows from other tables.

Introduction to Oracle INNER join syntax

In a relational database, data is distributed across a number of related tables. For example, in a sample database, sales order data is primarily stored in orders and order_items tables. Refer to the following ER diagram structure-

ordersThe table stores the header information for the order, and the order_items table stores the details of the order purchase goods.

The order ( orders ) table is linked to the order_id table by column order_items . This means that for orders each row in the table, we can order_items order_id find one or more row data with the same value in the table through the column.

To query data from two or more related tables, use the INNER JOIN clause. The following statement shows how to connect two tables T1 and T2 .

SELECT  *FROM  T1INNER JOIN T2 ON join_predicate;

Let's take a closer look at the above statement:

• First, in this case, FROM after the clause is the T1 specified primary table.
• Next, specify the condition in the INNER JOIN clause join_predicate . The join table in the above statement is T2 .
• Third, the JOIN predicate specifies the conditions for joining the table. Only row records that satisfy the join predicate are included in the result set.

T1 T2 A query returns a result set by using a column value that is based on the concatenated predicate phrase. It T1 compares each row of the table with T2 the rows of the table to find all the row records that satisfy the join predicate. As long as the join predicate is satisfied by matching a non- NULL value, the T1 T2 column values for each pair of matching rows of the table are merged into one row in the result set.

1. Oracle INNER Join Example

The following query uses INNER JOIN clauses to orders retrieve data from tables and order_items tables:

SELECT    *FROM    ordersINNER JOIN order_items ON    order_items.order_id = orders.order_idORDER BY    order_date DESC;

In this example, the JOIN predicate (condition) is-

order_items.order_id = orders.order_id

A query orders compares each row in a table to a order_items row in a table. When rows in two tables order_id have the same value in the column, the query merges the column values in the rows of the two tables into the result row and includes them in the result set.

So, execute the above query statement, get the following result-

2. Oracle INNER join example with using clause

In addition to ON clauses, you can use USING clauses to specify which columns to test for equality when you join a table.

USING INNER JOIN The syntax described in the following clause.

SELECT  *FROM  T1INNER JOIN T2 ON( c1, c2, ... );

Note that USING the columns listed in the clause, such as c1 and, c2 must T1 exist in the and T2 table (available).

The following example uses the INNER JOIN and USING clauses to orders retrieve data from tables and order_items tables:

SELECT  *FROM  ordersINNER JOIN order_items USING( order_id )ORDER BY  order_date DESC;

Execute the above example code to get the following results-

3. INNER join multi-table Join example

An INNER JOIN clause can join more than two tables. In practice, you should limit the number of join tables to improve performance. The following statement shows how to connect three tables:

The following statement shows how to connect three tables: orders , order_items and customers , to ensure the efficiency of query statements, more than three table connections are used with caution.

SELECT    name,    order_id,    order_date,    item_id,    product_id,    quantity,    unit_priceFROM    ordersINNER JOIN order_items    USING(order_id)INNER JOIN customers    USING(customer_id)ORDER BY    order_date DESC,    order_id DESC,    item_id ASC;

Execute the above query statement to get the following results-

The following example shows how to connect four tables: orders , order_items , customers and products . Refer to the following query statement-

SELECT  name AS customer_name,  order_id,  order_date,  item_id,  product_name,  quantity,  unit_priceFROM  ordersINNER JOIN order_items  USING(order_id)INNER JOIN customers  USING(customer_id)INNER JOIN products  USING(product_id)ORDER BY  order_date DESC,  order_id DESC,  item_id ASC;

Execute the above example query statement to get the following results-

In this tutorial, you learned how to use an Oracle inner JOIN to retrieve data records from a table that have matching rows from other tables.

Oracle Inner join clause (multi-table connection)