Oracle Joins

Summary: in this tutorial, you will learn various kind of Oracle joins that allow you to query data from two or more related tables.

Oracle join is used to combine columns from two or more tables based on values of the related columns. The related columns are typically the primary key column(s) of the first table and foreign key column(s) of the second table.

Oracle supports inner join, left join, right join, full outer join and cross join.

Note that you can join a table to itself to query hierarchical data using an inner join, left join, or right join. This kind of join is known as self-join.

Setting up sample tables

We will create two new tables with the same structure for the demonstration:

CREATE TABLE palette_a ( id INT PRIMARY KEY, color VARCHAR2 (100) NOT NULL ); CREATE TABLE palette_b ( id INT PRIMARY KEY, color VARCHAR2 (100) NOT NULL ); INSERT INTO palette_a (id, color) VALUES (1, 'Red'); INSERT INTO palette_a (id, color) VALUES (2, 'Green'); INSERT INTO palette_a (id, color) VALUES (3, 'Blue'); INSERT INTO palette_a (id, color) VALUES (4, 'Purple'); -- insert data for the palette_b INSERT INTO palette_b (id, color) VALUES (1, 'Green'); INSERT INTO palette_b (id, color) VALUES (2, 'Red'); INSERT INTO palette_b (id, color) VALUES (3, 'Cyan'); INSERT INTO palette_b (id, color) VALUES (4, 'Brown');
Code language: SQL (Structured Query Language) (sql)

The tables have some common colors such as Red and Green. Let’s call the palette_a the left table and palette_b the right table:

Oracle Join - left table
Oracle Join - right table

Oracle inner join

The following statement joins the left table to the right table using the values in the color column:

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a INNER JOIN palette_b b ON a.color = b.color;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Join - inner join example

As can be seen clearly from the result, the inner join returns rows from the left table that match with the rows from the right table.

The following Venn diagram illustrates an inner join when combining two result sets:

Oracle Joins - Inner Join

Oracle left join

The following statement joins the left table with the right table using a left join (or a left outer join):

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a LEFT JOIN palette_b b ON a.color = b.color;
Code language: SQL (Structured Query Language) (sql)

The output is shown as follows:

Oracle Join - left join example

The left join returns all rows from the left table with the matching rows if available from the right table. If there is no matching row found from the right table, the left join will have null values for the columns of the right table:

The following Venn diagram illustrates the left join:

Oracle Joins - Left Join

Sometimes, you want to get only rows from the left table that do not exist in the right table. To achieve this, you use the left join and a WHERE clause to exclude the rows from the right table.

For example, the following statement shows colors that only available in the palette_a but not palette_b:

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a LEFT JOIN palette_b b ON a.color = b.color WHERE b.id IS NULL;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Join - left join with a WHERE clause

The following Venn diagram illustrates the left join with the exclusion of rows from the right table:

Oracle Joins - Left Join with Where

Oracle right join

The right join or right outer join is a reversed version of the left join. The right join makes a result set that contains all rows from the right table with the matching rows from the left table. If there is no match, the left side will have nulls.

The following example use right join to join the left table to the right table:

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a RIGHT JOIN palette_b b ON a.color = b.color;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Join - right join example

The following Venn diagram illustrates the right join:

Oracle Joins - Right Join

Likewise, you can get only rows from the right table but not the left table by adding a WHERE clause to the above statement as shown in the following query:

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a RIGHT JOIN palette_b b ON a.color = b.color WHERE a.id IS NULL;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Oracle Join - right join with a WHERE clause

The following Venn diagram illustrates the right join with the exclusion of rows from the left table:

Oracle Joins - Right Join with Where

Oracle full outer join

Oracle full outer join or full join returns a result set that contains all rows from both left and right tables, with the matching rows from both sides where available. If there is no match, the missing side will have nulls.

The following example shows the full outer join of the left and right tables:

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a FULL OUTER JOIN palette_b b ON a.color = b.color;
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the result set of the full outer join:

oracle joins - full outer join example

Note that the OUTER keyword is optional.

The following Venn diagram illustrates the full outer join:

Oracle Joins - Full Outer Join

To get a set of rows that are unique from the left and right tales, you perform the same full join and then exclude the rows that you don’t want from both sides using a WHERE clause as follows:

SELECT a.id id_a, a.color color_a, b.id id_b, b.color color_b FROM palette_a a FULL JOIN palette_b b ON a.color = b.color WHERE a.id IS NULL OR b.id IS NULL;
Code language: SQL (Structured Query Language) (sql)

Here is the result:

Oracle Join - full outer join with a WHERE clause

The following Venn diagram illustrates the above operation:

Oracle Joins - Full Outer Join with Where

In this tutorial, you have learned how to use various kinds of Oracle joins to query data from two tables.

Was this tutorial helpful?