Oracle INNER JOIN

Summary: in this tutorial, you will learn about the Oracle INNER JOIN clause to retrieve rows from a table that has matching rows from other tables.

Introduction to Oracle INNER JOIN syntax

In a relational database, data is distributed in many related tables. For example, in the sample database, the sales order data is mainly stored in both orders and order_items tables.

The orders table stores the order’s header information and the order_items table stores the order line items.

The orders table links to the order_items table via the order_id column. It means that for each row in the orders table, you can find one or more rows in the order_items with the same values in the order_id column.

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

SELECT
  *
FROM
  T1
INNER JOIN T2 ON join_predicate;
Code language: SQL (Structured Query Language) (sql)

Let’s examine the statement above in detail:

• First, specify the main table in the FROM clause, T1 in this case.
• Second, specify the joined table in the INNER JOIN clause followed by a join_predicate. The joined table is T2 in the above statement.
• Third, a join predicate specifies the condition for joining tables. Only rows that satisfy the join predicate are included in the result set.

The query returns a result set by combining the column values of both tables T1 and T2 based on the join predicate.

It compares each row of the table T1 with rows of the table T2 to find all pairs of rows that satisfy the join predicate.

Whenever the join predicate is satisfied by matching non-NULL values, column values for each matching pair of rows T1 and T2 tables are combined into a row in the result set.

Oracle INNER JOIN example

The following query uses a INNER JOIN clause to retrieve data from the orders and order_items tables:

SELECT
    *
FROM
    orders
INNER JOIN order_items ON
    order_items.order_id = orders.order_id
ORDER BY
    order_date DESC;
Code language: SQL (Structured Query Language) (sql)

In this example, the join predicate is

order_items.order_id = orders.order_id
Code language: SQL (Structured Query Language) (sql)

The query compares each row in the orders table with rows in the order_items table. When rows from both tables have the same values in the order_id columns, the query combines column values from rows of both tables into a result row and includes it in the result set.

The following picture illustrates the result:

Oracle INNER JOIN with USING clause

Besides the ON clause, it is possible to use the USING clause to specify which columns to test for equality when joining tables.

The following illustrates the syntax of the INNER JOIN with the USING clause.

SELECT
  *
FROM
  T1
INNER JOIN T2 USING( c1, c2, ... );
Code language: SQL (Structured Query Language) (sql)

Note that the columns listed in the USING clause such as c1 and c2 must be available in both T1 and T2 tables.

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

SELECT
  *
FROM
  orders
INNER JOIN order_items USING( order_id )
ORDER BY
  order_date DESC;
Code language: SQL (Structured Query Language) (sql)

Oracle INNER JOIN – joining multiple tables

Consider the following tables orders,  order_items, customers, and products from the sample database.

The inner join clause can join more than two tables. In practice, you should limit the number of joined tables to avoid the performance issue.  The following statement shows how to join three tables:orders, order_items, and customers.

SELECT
  name AS customer_name,
  order_id,
  order_date,
  item_id,
  quantity,
  unit_price
FROM
  orders
INNER JOIN order_items USING(order_id)
INNER JOIN customers USING(customer_id)
ORDER BY
  order_date DESC,
  order_id DESC,
  item_id ASC;Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

The following example illustrates how to join four tables: orders,  order_items, customers, and products.

SELECT
	name AS customer_name,
	order_id,
	order_date,
	item_id,
	product_name,
	quantity,
	unit_price
FROM
	orders
INNER 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;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle inner join to retrieve rows from a table that has matching rows from other tables.