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 table stores the order’s header information and the
order_items table stores the order line items.
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
To query data from two or more related tables, you use the
INNER JOIN clause. The following statement illustrates how to join two tables
SELECT * FROM T1 INNER JOIN T2 ON join_predicate;
Let’s examine the statement above in detail:
- First, specify the main table in the
T1in this case.
- Second, specify the joined table in the
INNER JOINclause followed by a
join_predicate. The joined table is
T2in 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
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
T2 tables are combined into a row in the result set.
INNER JOIN example
The following query uses a
INNER JOIN clause to retrieve data from the
SELECT * FROM orders INNER JOIN order_items ON order_items.order_id = orders.order_id ORDER BY order_date DESC;
In this example, the join predicate is
order_items.order_id = orders.order_id
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:
INNER JOIN with
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
SELECT * FROM T1 INNER JOIN T2 ON( c1, c2, ... );
Note that the columns listed in the
USING clause such as
c2 must be available in both
The following example uses the
INNER JOIN with
USING clause to retrieve data from
SELECT * FROM orders INNER JOIN order_items USING( order_id ) ORDER BY order_date DESC;
INNER JOIN – joining multiple tables
Consider the following tables
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:
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;
Here is the partial result set:
The following example illustrates how to join four tables:
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;
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.