Oracle Joins With Examples

Oracle Joins are used when we want to retrieve data from two or more tables.

The process of storing data that is related to each other in different Oracle Tables to reduce redundancy of data and to save storage space is called Database Normalization. Often we need to combine or join related data that is scattered across different tables and display it as a single result set and for this purpose we use Joins.

Some of the joins used in Oracle are as follows:

1. Natural Join
2. Inner Join ( Or simply JOIN )
3. Left Join or ( Left Outer Joiner )
4. Right Join or ( Right Outer Join )
5. Full Outer Join
6. Self Join
7. Cross Join or ( Cartesian Product )

Self Join is the only join which is used for joining the table with itself. Let’s understand these joins with the help of examples :

Examples

Let’s create two sample tables for understanding how each join works:

CREATE TABLE food_a (
    id INT PRIMARY KEY,
    food VARCHAR2 (50) NOT NULL
);

CREATE TABLE food_b (
    id INT PRIMARY KEY,
    color VARCHAR2 (50) NOT NULL
);

-- insert data in food_a table

INSERT INTO food_a (id, item)
VALUES (1, 'Pizza');

INSERT INTO food_a (id, item)
VALUES (2, 'Burger');

INSERT INTO food_a (id, item)
VALUES (3, 'Soup');

INSERT INTO food_a (id, item)
VALUES (4, 'Pasta');

-- insert data in food_b table

INSERT INTO food_b (id, item)
VALUES (1, 'Pizza');

INSERT INTO food_b (id, item)
VALUES (2, 'Burger');

INSERT INTO food_b (id, item)
VALUES (3, 'Cake');

INSERT INTO food_b (id, item)
VALUES (4, 'Salad');

After executing the queries we get these two tables.

Here we can assume that food_a is the left table and food_b is the right table. Pizza and Burger are the items which are common in both table, while the other items are unique to that table. Both tables have 4 rows each.

1. Natural Join

Natural Join is used for joining two tables based on common columns provided they have same name and data type. By default, the result set will contain only one copy of matching columns

SELECT
    *
FROM
    food_a a
NATURAL JOIN food_b b;

Result:

The query returns only two rows having matching values in both columns. While there is only one copy of common column i,e, ‘ID’.

2. Inner Join

Inner Join can also be simply called as Join.

We use the following query for combining both left and right tables that we created above:

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
INNER JOIN food_b b ON a.item = b.item;

Here we have used table alias for food_a as ‘a’ and for food_b as ‘b’. The columns after SELECT statement are mentioned as table_alias.column_name column_alias. Both the tables are joined using the common column ‘item’ in both the tables.

Result:

The Inner Join query returns the items which are present in both the left and right table. From this we can see that Inner Join is used for returning rows from left table that match with rows from right table.

The following Venn diagram illustrates how Inner Join returns a result that is the intersection of both tables.

Difference Between NATURAL JOIN and INNER JOIN:

1. NATURAL JOIN joins two tables on the basis of same attribute name and datatype while INNER JOIN, joins the two tables on the basis of column that we specify explicitly.
2. The result set contains matching values from both tables and just one copy of common column in case of NATURAL JOIN. The result contains two copies of common column and duplicates of matching values in case of INNER JOIN.
3. If we don’t give any condition in NATURAL JOIN, then it automatically finds the matching attribute with same datatype and perform the join operation. In case of INNER JOIN, we need to explicitly specify the common column in Join condition.

3. Left Join or Left Outer Join

The following query is used for joining the left and right table by using LEFT JOIN or Left Outer Join.

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
LEFT JOIN food_b b ON a.item = b.item;

Result:

The LEFT JOIN query returns all rows from the left table and only matching rows from the right table. The rows in right table which do not have a matching value return NULL.

This can be illustrated using this Venn Diagram:

Suppose, we want the values which are only present in the left table and do not exist in the right table. We combine a WHERE clause to filter the result and use it with LEFT JOIN in this query. Let’s check this out:

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
LEFT JOIN food_b b ON a.item = b.item
WHERE b.id IS NULL;

Result:

The query returns the rows which are unique to the left table and are not present in the right table. We combine a WHERE clause to filter the result and use it with LEFT JOIN in this query.

This can be illustrated in the following Venn Diagram.

4. Right Join or Right Outer Join

Just like the Left Join, we will now see how to combine two tables using Right Join or Right Outer Join.

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
RIGHT JOIN food_b b ON a.item = b.item;

Result:

The RIGHT JOIN query returns all rows from the right table and only matching rows from the left table.

In case there is no match in the left table corresponding to a value in right table, a NULL value is returned. In this case, rows 3 and 4 in left table do not match with 3rd and 4th rows of right table and hence return NULL.

This can be seen in the following Venn Diagram:

Now, if we want to get values which are only unique to the right table then we combine WHERE clause with RIGHT JOIN as shown in the following query:

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
RIGHT JOIN food_b b ON a.item = b.item
WHERE a.id IS NULL;;

Result:

The query returns values which are only present in the right table and not present in left table.

This can also be seen in the following Venn Diagram:

5. Oracle Full Outer Join

A Full Outer Join is used when we combine both the tables and display all values. If there is a matching value in the other table, then values from both left and table will be shown and in case there is no matching value in other table, then a NULL is returned.

Let’s see the following query:

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
FULL OUTER JOIN food_b b ON a.item = b.item;

Result:

The result set contains all rows from both the table and shows NULL values in absence of matching value.

This can be in the following Venn Diagram:

Suppose, we want to find rows which are unique to each table and not present in other table, then we combine FULL OUTER JOIN with WHERE clause to filter out the matching rows and only show unique rows in each table. Let’s see this query:

SELECT
    a.id id_a,
    a.item item_a,
    b.id id_b,
    b.item item_b
FROM
    food_a a
FULL OUTER JOIN food_b b ON a.item = b.item
WHERE a.id IS NULL OR b.id IS NULL;

Result:

The query returns unique rows in each table.

This can also be seen in the following Venn Diagram:

6. Cross Join

Cross Join is used for finding the Cartesian Product of two tables. If each table contains 3 rows, then the result will contain 3 * 3 = 9 rows. As each row from left table, is matched with each row from right table.

Let’s see the following query:

SELECT
    a.id,
    a.item,
    b.id,
    b.item
FROM
    food_a a
CROSS JOIN food_b b;

Result:

Since, our left and right tables both contain 4 rows each, the resultant output from Cross Join Query gives us 4 * 4 = 16 rows in total. Each row in left table is combined with each row from right table.

In this article, we learned how to use different types of joins in Oracle.