Joins in ORACLE SQL

In Oracle SQL, joins are used to retrieve data from multiple tables based on relationships between them. Here are the main types of joins:

1. Inner Join

An inner join returns rows where there is a match in both tables based on a specified condition.

Rows without matching values in both tables are excluded.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

This query returns only the employees who belong to a department.

1. Left Join (Left Outer Join)

A left join returns all rows from the left (first) table and matched rows from the right (second) table.

If there is no match, NULL values are shown for columns from the right table.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

This query returns all employees, including those without a department, with NULL for dept_name if there's no match.

1. Right Join (Right Outer Join)

A right join returns all rows from the right (second) table and matched rows from the left (first) table.

If there is no match, NULL values are shown for columns from the left table.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id;

This query returns all departments, including those without employees, with NULL for employee details if there's no match.

1. Full Outer Join

A full outer join returns all rows when there is a match in either the left or right table.

If there’s no match in one of the tables, NULL values are shown for columns from that table.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.dept_id = d.dept_id;

This query returns all employees and departments, with NULL values where there are no matches.

1. Cross Join

A cross join returns the Cartesian product of the two tables, meaning every row in the first table is paired with every row in the second table.

No join condition is needed; however, it can produce a large number of rows.

SELECT e.emp_id, e.emp_name, d.dept_name
FROM Employees e
CROSS JOIN Departments d;

This query returns every possible combination of employees and departments.

1. Self Join

A self join is when a table is joined with itself. It's useful for comparing rows within the same table.

SELECT e1.emp_id AS emp1_id, e2.emp_id AS emp2_id, e1.emp_name AS emp1_name, e2.emp_name AS emp2_name
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.emp_id;

This query retrieves employees along with their managers by joining the Employees table to itself.

1. Natural Join

A natural join automatically joins tables based on columns with the same names and compatible data types in both tables.

SELECT *
FROM Employees
NATURAL JOIN Departments;

This query automatically joins Employees and Departments based on the common column dept_id.

These join types allow you to retrieve related data across multiple tables in Oracle SQL efficiently.