Oracle Joins FAQs with Examples

1. What is a Join in Oracle?

A join combines rows from two or more tables based on a related column. It is commonly used to retrieve related data stored in different tables.

Example Tables

EMPLOYEES

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID
101	John	10
102	David	20
103	Scott	30

DEPARTMENTS

DEPARTMENT_ID	DEPARTMENT_NAME
10	Sales
20	HR
30	Finance

Query

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Output

EMPLOYEE_NAME	DEPARTMENT_NAME
John	Sales
David	HR
Scott	Finance

2. Why do we use Joins?

Joins are used to:

• Retrieve related data from multiple tables
• Avoid duplicate data by following database normalization
• Generate reports
• Improve data organization
• Maintain relationships between tables

3. What are the different types of joins in Oracle?

Oracle supports:

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Cross Join
• Self Join
• Natural Join

4. What is an Inner Join?

An Inner Join returns only the rows that have matching values in both tables.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

Only matching records are returned.

5. What is a Left Outer Join?

A Left Outer Join returns all rows from the left table, matching rows from the right table, and NULL when no match exists.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

6. What is a Right Outer Join?

A Right Outer Join returns all rows from the right table, matching rows from the left table, and NULL for unmatched rows from the left table.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

7. What is a Full Outer Join?

A Full Outer Join returns all matching rows and all non-matching rows from both tables, with NULL where there is no matching row.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

8. What is a Cross Join?

A Cross Join returns the Cartesian product of two tables. If Employees = 3 rows and Departments = 4 rows, the result is 12 rows.

Example:

SELECT *
FROM employees
CROSS JOIN departments;

9. What is a Cartesian Product?

A Cartesian product occurs when every row from one table is combined with every row from another table. It can happen intentionally with CROSS JOIN or unintentionally if a join condition is omitted.

10. What is a Self Join?

A Self Join joins a table to itself. It is commonly used for hierarchical relationships such as employees and managers.

Example:

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;

11. What is a Natural Join?

A Natural Join automatically joins tables based on columns with the same name and compatible data types.

Example:

SELECT employee_name,
       department_name
FROM employees
NATURAL JOIN departments;

Note: Explicit JOIN ... ON syntax is generally preferred because it is clearer and less likely to break if table structures change.

12. Difference between INNER JOIN and OUTER JOIN

INNER JOIN	OUTER JOIN
Returns only matching rows	Returns matching and non-matching rows depending on LEFT, RIGHT, or FULL
Unmatched rows are excluded	Unmatched rows are included with NULL values

13. Difference between LEFT JOIN and RIGHT JOIN

LEFT JOIN	RIGHT JOIN
Returns all rows from the left table	Returns all rows from the right table
Missing matches from the right appear as NULL	Missing matches from the left appear as NULL

14. What is the old Oracle outer join syntax?

Oracle previously used the (+) operator.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e,
     departments d
WHERE e.department_id = d.department_id(+);

This is equivalent to:

SELECT e.employee_name,
       d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

The ANSI JOIN syntax is recommended for new development.

15. Can more than two tables be joined?

Yes.

Example:

SELECT e.employee_name,
       d.department_name,
       l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

16. Can joins use multiple columns?

Yes.

Example:

SELECT *
FROM table1 t1
JOIN table2 t2
ON t1.emp_id = t2.emp_id
AND t1.department_id = t2.department_id;

17. Can joins use aliases?

Yes. Aliases improve readability.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

18. Can joins use WHERE conditions?

Yes.

Example:

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

19. Can joins use aggregate functions?

Yes.

Example:

SELECT d.department_name,
       COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_name;

20. Can joins be used with subqueries?

Yes.

Example:

SELECT *
FROM employees e
JOIN
(
  SELECT department_id,
         department_name
  FROM departments
) d
ON e.department_id = d.department_id;

21. What are Equi Join and Non-Equi Join?

Equi Join uses the equality operator (=).

SELECT *
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Non-Equi Join uses operators other than = such as <, >, BETWEEN, or <=.

SELECT e.employee_name,
       g.grade
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary
AND g.max_salary;

22. What are the advantages of Joins?

• Retrieve related data from multiple tables
• Reduce data redundancy
• Improve database normalization
• Support complex reporting
• Enable efficient data retrieval when appropriate indexes are present

23. What are common Join errors?

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00918	Column ambiguously defined
ORA-01427	Single-row subquery returns more than one row
ORA-01722	Invalid number

24. What is the difference between JOIN and UNION?

JOIN	UNION
Combines columns from related tables	Combines rows from compatible queries
Uses related columns	Requires the same number of columns with compatible data types
Returns one wider result set	Returns one taller result set

25. Real-Time Example

Suppose an HR system stores employee information separately from department information.

EMPLOYEES

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID
101	John	10
102	David	20

DEPARTMENTS

DEPARTMENT_ID	DEPARTMENT_NAME
10	Sales
20	HR

Query

SELECT e.employee_name,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id;

Result

EMPLOYEE_NAME	DEPARTMENT_NAME
John	Sales
David	HR

Key Point:
Joins are one of the most important SQL concepts in Oracle because they let you combine related data from multiple tables efficiently and clearly.