Outer Join - (Oracle Joins)

In Oracle SQL, an outer join is used to combine rows from two or more tables, based on a related column between them. It retrieves all the rows from one table and matching rows from another table.

Syntax

The syntax for performing an outer join in Oracle SQL is as follows:

SELECT *
FROM table1
LEFT [ OUTER ] JOIN table2
ON table1.column_name = table2.column_name

Here, table1 and table2 are the names of the tables we want to join, and column_name is the related column between these two tables.

The LEFT JOIN keyword means that we are retrieving all the rows from the left table (table1) and matching rows from the right table (table2). The OUTER keyword is optional.

Other types of outer joins include RIGHT JOIN, which retrieves all the rows from the right table and matching rows from the left table, and FULL OUTER JOIN, which retrieves all the rows from all the tables.

Example

Consider two tables customers and orders, with the following data:

Customers table:
customer_id | customer_name
------------|--------------
1           | John
2           | Jane
3           | Mike

Orders table:
order_id    | customer_id | order_date
------------|-------------|-----------
1           | 1           | 2021-05-01
2           | 2           | 2021-05-02
3           | 1           | 2021-05-03

We can perform a left outer join on these two tables as follows:

SELECT *
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This will produce the following result:

customer_id | customer_name | order_id | customer_id | order_date
------------|---------------|----------|------------|------------
1           | John          | 1        | 1          | 2021-05-01
1           | John          | 3        | 1          | 2021-05-03
2           | Jane          | 2        | 2          | 2021-05-02
3           | Mike          | null     | null       | null

Output

| customer_id | customer_name | order_id | customer_id | order_date |
| ----------- | ------------- | --------| -----------|------------|
|         1   |         John  |     1   |          1 | 2021-05-01 |
|         1   |         John  |     3   |          1 | 2021-05-03 |
|         2   |         Jane  |     2   |          2 | 2021-05-02 |
|         3   |         Mike  |  null  |     null   |    null    |

Explanation

In the above example, we have combined the customers and orders tables using a left outer join. This means that we have retrieved all the rows from the customers table and matching rows from the orders table based on the customer_id column. If there is no matching row in the orders table, the columns from the orders table will be null.

Use

Outer joins are used to combine rows from two or more tables when we want to include all the rows from one table, even if there are no matching rows in the other table(s). This is useful when we have optional relationships between tables.

Important Points

• An outer join combines rows from two or more tables based on a related column.
• Oracle SQL supports different types of outer joins, including LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
• Outer joins are useful when we want to include all the rows from one table, even if there are no matching rows in the other table(s).

Summary

In summary, an outer join is used in Oracle SQL to combine rows from two or more tables based on a related column. The left outer join retrieves all the rows from the left table and matching rows from the right table. This is useful when we have optional relationships between tables.