In Oracle PL/SQL, a JOIN operation is used to combine data from two or more tables based on a related column between them. There are different types of JOIN operations available in Oracle PL/SQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.
Here’s an explanation of each of these JOIN types with an example:
- INNER JOIN: An inner join returns only the matching rows from both tables based on the common column(s).
For example, let’s assume we have two tables named “employees” and “departments” with the following data:
We can use the following query to join these two tables based on the dept_id column:
The above query will return the following result:
- LEFT JOIN: A left join returns all the rows from the left table and the matching rows from the right table based on the common column(s). If there are no matching rows in the right table, NULL values will be returned.
For example, let’s assume we have the same two tables as before, and we want to retrieve all the employees and their department names, even if they don’t have a department assigned:
The above query will return the following result:
In this result, we can see that the employee Mike doesn’t have a department assigned, so the dept_name value is NULL.
- RIGHT JOIN: A right join returns all the rows from the right table and the matching rows from the left table based on the common column(s). If there are no matching rows in the left table, NULL values will be returned.
For example, let’s use the same two tables as before, but this time we want to retrieve all the departments and their employee names, even if there are no employees in the department:
The above query will return the following result:
In this result, we can see that the department HR doesn’t have any employees assigned, so the name value is NULL.
- FULL OUTER JOIN: A full outer join returns all the rows from both tables, including the non-matching rows. If there are no matching rows in one or both tables, NULL values will be returned.
For example, let’s use the same two tables as before, but this time we want to retrieve all the employees and departments, even if they don’t match:
The above query will return the following result:
- CROSS JOIN: A cross join returns all the possible combinations of rows from both tables. It doesn’t require any common column(s) between the tables.
For example, let’s assume we have two tables named “colors” and “sizes” with the following data:
We can use the following query to get all the possible combinations of colors and sizes:
The above query will return the following result:
In this result, we can see that all the possible combinations of colors and sizes have been returned.
It’s worth noting that a cross join can return a very large number of rows if the tables being joined have many rows. Therefore, it’s important to use it carefully and to filter the results appropriately if needed.
