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 orders data is mainly stored in both orders and order_items tables.

Oracle INNER JOIN - 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 column values of both tables T1 and T2 based on the join predicate. It compares each row of table T1 with rows of 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 of 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 include it in the result set.

The following picture illustrates the result:

Oracle INNER JOIN - joining two tables example

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 two tables example

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:

oracle inner join - join three tables

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)
Oracle INNER JOIN - joining four tables example

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

Was this tutorial helpful?