8 Joins and Subqueries in Oracle SQL
Key Concepts
Joins and subqueries are essential tools in Oracle SQL for combining data from multiple tables and performing complex queries. Understanding these concepts is crucial for effectively querying and analyzing data in a relational database.
1. Inner Join
An inner join returns only the rows that have matching values in both tables. It is the most common type of join and is used to combine data based on a related column between them.
Example: Joining the "Employees" and "Departments" tables on the "DepartmentID" column:
SELECT Employees.EmployeeID, Employees.FirstName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
2. Left Outer Join
A left outer join returns all the rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.
Example: Retrieving all employees and their department names, including those without a department:
SELECT Employees.EmployeeID, Employees.FirstName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
3. Right Outer Join
A right outer join returns all the rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.
Example: Retrieving all departments and their employees, including departments without employees:
SELECT Employees.EmployeeID, Employees.FirstName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
4. Full Outer Join
A full outer join returns all the rows from both tables. If there is no match, the result is NULL on the side where there is no match.
Example: Retrieving all employees and departments, including those without a match:
SELECT Employees.EmployeeID, Employees.FirstName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
5. Self Join
A self join is a join of a table to itself. It is used to query hierarchical data or to compare rows within the same table.
Example: Retrieving employees and their managers from the same "Employees" table:
SELECT e.EmployeeID, e.FirstName, m.FirstName AS ManagerName
FROM Employees e
JOIN Employees m ON e.ManagerID = m.EmployeeID;
6. Cross Join
A cross join returns the Cartesian product of the joined tables. It combines each row from the first table with each row from the second table.
Example: Retrieving all possible combinations of employees and departments:
SELECT Employees.EmployeeID, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;
7. Subqueries
A subquery is a query nested inside another query. It can be used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
Example: Retrieving employees who earn more than the average salary:
SELECT EmployeeID, FirstName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
8. Correlated Subqueries
A correlated subquery is a subquery that depends on the outer query for its values. It is executed once for each row processed by the outer query.
Example: Retrieving departments where the average salary is higher than the average salary of all employees:
SELECT DepartmentID, DepartmentName
FROM Departments d
WHERE (SELECT AVG(Salary) FROM Employees e WHERE e.DepartmentID = d.DepartmentID) > (SELECT AVG(Salary) FROM Employees);
By mastering these joins and subqueries, you can effectively combine and analyze data from multiple tables in Oracle SQL, making your queries more powerful and flexible.