Oracle SQL is one of the most powerful tools for managing and querying relational databases. One of the fundamental concepts in SQL is the JOIN operation, which allows you to combine rows from two or more tables based on related columns.
In this blog, we will dive deep into the different types of JOINs in Oracle SQL, explain their purpose, and demonstrate how to use them effectively using the Oracle HR schema. The HR schema is a classic sample schema that contains tables such as EMPLOYEES, DEPARTMENTS, JOBS, LOCATIONS, and more, making it perfect for illustrating JOIN concepts.
We will cover the following types of JOINs:
- INNER JOIN
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- CROSS JOIN
- SELF JOIN
Understanding Joins
A JOIN is used to retrieve related data spread across multiple tables. Since relational databases normalize data, it's common to find pieces of information stored in different tables. JOINs let us combine this data logically.
The most common key used to join tables in the HR schema is department_id or job_id.
1. INNER JOIN
Definition:
An INNER JOIN returns rows when there is a match in both tables. Rows without matching keys in either table are excluded.
List Employees with Their Department Names
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
Explanation:
This query fetches employee names along with their department names. The INNER JOIN ensures only employees assigned to a department are returned. If an employee has no department assigned (department_id is NULL), they will be excluded.
List Employees and Their Job Titles
SELECT e.first_name, e.last_name, j.job_title
FROM employees e
INNER JOIN jobs j ON e.job_id = j.job_id;
Explanation:
This query retrieves each employee’s name with their job title by joining the EMPLOYEES table with the JOBS table on the job_id. Only employees with valid jobs are listed.
2. LEFT OUTER JOIN (or LEFT JOIN)
Definition:
A LEFT OUTER JOIN returns all rows from the left table and matched rows from the right table. Rows in the left table without matches get NULLs in the right table columns.
Example 1: List All Departments and Their Employees (Include Departments with No Employees)
SELECT d.department_name, e.first_name, e.last_name
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id;
Explanation:
This query shows every department with its employees. Departments without employees will appear with NULL values in employee columns. Useful for identifying vacant departments.
Example 2: List All Jobs and Employees Holding Them (Include Jobs with No Employees)
SELECT j.job_title, e.first_name, e.last_name
FROM jobs j
LEFT JOIN employees e ON j.job_id = e.job_id;
Explanation:
This query lists every job title with employees who have that job. If no employee holds a particular job, the employee columns will be NULL. Helps HR track unfilled job roles.
3. RIGHT OUTER JOIN (or RIGHT JOIN)
Definition:
A RIGHT OUTER JOIN returns all rows from the right table and matched rows from the left table. Rows in the right table without matches get NULLs in left table columns.
Example 1: List All Employees and Their Departments (Including Employees Without Departments)
SELECT e.first_name, e.last_name, d.department_name
FROM departments d
RIGHT JOIN employees e ON d.department_id = e.department_id;
Explanation:
This query returns all employees and their department names. Employees who are not assigned to any department will appear with NULL in the department_name column. This is the opposite of a LEFT JOIN.
List All Employees and Their Jobs (Including Employees Without Job Assignments)
SELECT e.first_name, e.last_name, j.job_title
FROM jobs j
RIGHT JOIN employees e ON j.job_id = e.job_id;
Explanation:
This query lists all employees with their job titles, including those without an assigned job. Employees without job records will have NULL in the job_title column.
---
4. FULL OUTER JOIN
Definition:
A FULL OUTER JOIN returns all rows when there is a match in one of the tables. Rows without a match in either table will have NULLs for columns of the missing side.
List All Employees and Departments Including Unmatched Rows
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
Explanation:
This query returns all employees and all departments, matching where possible. Employees without departments and departments without employees are included, showing NULLs where data is missing.
List All Jobs and Employees Including Unmatched Rows
SELECT j.job_title, e.first_name, e.last_name
FROM jobs j
FULL OUTER JOIN employees e ON j.job_id = e.job_id;
Explanation:
This query lists all jobs and employees, including jobs without employees and employees without jobs. It provides a complete view of job assignments and vacancies.
5. CROSS JOIN
Definition:
A CROSS JOIN returns the Cartesian product of the two tables, i.e., every combination of rows from both tables.
List All Possible Employee and Department Combinations
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;
Explanation:
This query returns every possible pairing of employees and departments, regardless of whether they belong to that department. It’s useful for analysis that requires considering all combinations.
List All Possible Employee and Job Title Combinations
SELECT e.first_name, e.last_name, j.job_title
FROM employees e
CROSS JOIN jobs j;
Explanation:
This query produces all combinations of employees and job titles, which might be used for modeling or planning possible role assignments.
6. SELF JOIN
Definition:
A SELF JOIN is when a table is joined to itself. This is useful for hierarchical or comparative data within the same table.
Find Employees and Their Managers
SELECT e.first_name AS employee_first_name, e.last_name AS employee_last_name,
m.first_name AS manager_first_name, m.last_name AS manager_last_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;
Explanation:
This query retrieves employees along with their managers by joining the EMPLOYEES table to itself on manager IDs. Employees without managers will show NULL for manager fields.
Find Employees Working in the Same Department
SELECT e1.first_name AS employee1_first, e1.last_name AS employee1_last,
e2.first_name AS employee2_first, e2.last_name AS employee2_last,
e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id != e2.employee_id;
Explanation:
This query lists pairs of employees working in the same department but excludes pairing an employee with themselves. Useful for team or peer analysis.
Conclusion
JOINs are essential tools in Oracle SQL that allow you to combine data across multiple tables based on relationships. By mastering INNER JOIN, OUTER JOINs, CROSS JOIN, and SELF JOINs, you can unlock complex data relationships in the HR schema or any relational database.
Using the HR schema examples, we demonstrated practical scenarios to:
- Retrieve employee and department/job information,
- Identify unmatched records,
- Explore all possible combinations,
- Analyze hierarchical employee relationships.
Keep practicing these JOIN types to improve your SQL skills and build more insightful queries for your organization.
Next Steps:
- Try writing queries combining multiple JOIN types.
- Experiment with filtering and aggregation on JOIN results.
- Explore analytic functions with JOINs for advanced reporting.
Thank you for reading. For any questions or feedback please add in comment section.
0 Comments