Oracle INNER JOIN

Summary: in this tutorial, you will learn about 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 in many related tables. For example, in the sample database, the sales order data is mainly stored in the orders and order_items tables.

In this diagram:

• The orders table stores the order header.
• 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 related tables, you use the INNER JOIN clause.

Here’s the syntax of the INNER JOIN clause:

SELECT
  column_list
FROM
  X
  INNER JOIN Y ON X.id = Y.id;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, provide the first table (X) in the FROM clause.
• Second, specify the second table in the INNER JOIN clause (Y)
• Third, define a join condition (join_condition) after the ON keyword in the INNER JOIN clause. A join condition specifies the condition for joining the two tables. The INNER JOIN will include rows that satisfy the join condition in the result set.

Oracle evaluates the FROM clause first, then the INNER JOIN clause, and finally the SELECT clause.

Note that you can use other clauses of the SELECT statement such as a WHERE clause.

How inner join works:

• First, the INNER JOIN compares values in the id columns of both tables X and Y.
• Second, the INNER JOIN merges the matching rows from both tables in an intermediate table if they are equal.
• Third, the SELECT clause returns the specified columns (column_list) in the final result set.

Understanding Oracle Inner Join #

Suppose you have two tables X and Y, and want to merge rows from both tables using an inner join:

• The X table has two columns: id (key) and x.
• The Y table has two columns: id (key) and y.

The inner join includes the rows from both tables that have matching values in the id columns. It does not include unmatching rows in the result set:

The following Venn diagram is another way to illustrate how an Oracle inner join works:

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)

Try it

Output:

In this example, the join condition is

order_items.order_id = orders.order_idCode 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.

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
  X
INNER JOIN Y 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 X and Y 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)

Try it

Output:

Joining more than two tables #

The following diagram displays four tables orders,  order_items, customers, and products from the sample database.

To join more than two tables, you can use multiple inner join clauses in the SELECT statement.

For example, the following statement uses two inner join clauses 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)

Try it

Output:

The following statement uses three inner join clauses 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)

Try it

Output:

Summary #

• Use the Oracle INNER JOIN clause to retrieve rows from a table that have matching rows in another table.

Quiz #