Oracle CROSS JOIN FAQs with Examples

1. What is a CROSS JOIN in Oracle?

A CROSS JOIN returns the Cartesian product of two tables. Every row from the first table is combined with every row from the second table.

Example:

• If Table A has 3 rows
• If Table B has 4 rows
• The result contains 3 × 4 = 12 rows

Syntax:

SELECT *
FROM table1
CROSS JOIN table2;

2. Why do we use a CROSS JOIN?

A CROSS JOIN is used to:

• Generate all possible combinations of rows
• Create test data
• Produce combinations such as products and colors
• Generate calendars or schedules
• Create matrices for reporting

3. What is the syntax of CROSS JOIN?

SELECT column_list
FROM table1
CROSS JOIN table2;

Example:

SELECT employee_name,
       department_name
FROM employees
CROSS JOIN departments;

4. How does CROSS JOIN work?

Suppose the tables are:

EMPLOYEES

EMPLOYEE_ID	EMPLOYEE_NAME
101	John
102	David

DEPARTMENTS

DEPARTMENT_NAME
Sales
HR

Query:

SELECT employee_name,
       department_name
FROM employees
CROSS JOIN departments;

Output:

EMPLOYEE	DEPARTMENT
John	Sales
John	HR
David	Sales
David	HR

Every employee is paired with every department.

5. What is a Cartesian Product?

A Cartesian product is the result of combining every row from one table with every row from another table.

Formula: Rows Returned = Rows in Table A × Rows in Table B

6. Is an ON clause used with CROSS JOIN?

No. A CROSS JOIN does not use an ON condition because it intentionally combines every row from both tables.

Correct:

SELECT *
FROM employees
CROSS JOIN departments;

Incorrect:

SELECT *
FROM employees
CROSS JOIN departments
ON employees.department_id = departments.department_id;

7. How many rows does a CROSS JOIN return?

Formula: Rows = Table1 × Table2

Employees	Departments	Result
5	3	15
100	20	2000
1000	50	50000

8. Can CROSS JOIN be used with more than two tables?

Yes.

Example:

SELECT *
FROM employees
CROSS JOIN departments
CROSS JOIN locations;

If Employees = 10, Departments = 5, and Locations = 4, the result is 10 × 5 × 4 = 200 rows.

9. What is the difference between CROSS JOIN and INNER JOIN?

CROSS JOIN	INNER JOIN
Returns all possible combinations	Returns only matching rows
No join condition	Requires an ON or USING clause
Produces Cartesian product	Produces related data
Can generate very large result sets	Typically returns fewer rows

10. What is the difference between CROSS JOIN and FULL OUTER JOIN?

CROSS JOIN	FULL OUTER JOIN
Returns every possible combination	Returns matching and non-matching rows
No relationship required	Requires a join condition
Cartesian product	Outer join

11. Can a WHERE clause be used with CROSS JOIN?

Yes. The WHERE clause filters the result after the Cartesian product is created.

Example:

SELECT employee_name,
       department_name
FROM employees
CROSS JOIN departments
WHERE department_name = 'Sales';

12. Can CROSS JOIN use aliases?

Yes.

SELECT e.employee_name,
       d.department_name
FROM employees e
CROSS JOIN departments d;

13. Can CROSS JOIN be combined with other joins?

Yes.

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

14. What happens if large tables are used?

The result size can become extremely large.

Table A	Table B	Rows Returned
10,000	5,000	50,000,000

This may consume significant CPU, memory, temporary space, and execution time.

15. What are common uses of CROSS JOIN?

• Product combinations
• Size and color combinations
• Calendar generation
• Scheduling
• Test data creation
• Matrix reports

16. Real-Time Example – Product Variants

PRODUCTS

PRODUCT_NAME
Laptop
Mobile

COLORS

COLOR_NAME
Black
Silver

Query:

SELECT product_name,
       color_name
FROM products
CROSS JOIN colors;

Output:

PRODUCT	COLOR
Laptop	Black
Laptop	Silver
Mobile	Black
Mobile	Silver

17. Can CROSS JOIN produce duplicate rows?

A CROSS JOIN itself does not create duplicates beyond the combinations implied by the source data. If either input table contains duplicate rows, those duplicates participate in the combinations and may result in repeated-looking output.

18. What are the advantages of CROSS JOIN?

• Generates all possible combinations
• Simple syntax
• Useful for test data generation
• Helpful for reporting and matrix creation
• Works with multiple tables

19. What are the disadvantages of CROSS JOIN?

• Can generate extremely large result sets
• May reduce query performance
• High CPU and memory usage for large tables
• Often used accidentally when a join condition is omitted in older comma-style joins

20. What are common errors related to CROSS JOIN?

Error	Cause
ORA-00942	Table or view does not exist
ORA-00904	Invalid column name
ORA-00918	Column ambiguously defined
ORA-00933	SQL command not properly ended

21. Is CROSS JOIN the same as omitting the join condition?

Using the old comma-separated syntax without a join condition produces a Cartesian product, which is functionally equivalent to a CROSS JOIN.

Example:

SELECT *
FROM employees,
     departments;

Equivalent ANSI syntax:

SELECT *
FROM employees
CROSS JOIN departments;

Using the explicit CROSS JOIN syntax makes the intent clearer and is generally preferred.

22. Can CROSS JOIN be used with DUAL?

Yes.

SELECT level,
       dummy
FROM
(
  SELECT LEVEL
  FROM dual
  CONNECT BY LEVEL <= 3
)
CROSS JOIN dual;

23. Real-Time Example – Employee Training Schedule

Suppose every employee must attend every available training course.

EMPLOYEES

EMPLOYEE_NAME
John
David

COURSES

COURSE_NAME
SQL
PL/SQL
Oracle DBA

Query:

SELECT e.employee_name,
       c.course_name
FROM employees e
CROSS JOIN courses c;

Output:

EMPLOYEE	COURSE
John	SQL
John	PL/SQL
John	Oracle DBA
David	SQL
David	PL/SQL
David	Oracle DBA

24. CROSS JOIN vs Other Joins

Join Type	Returns
INNER JOIN	Only matching rows
LEFT OUTER JOIN	All left rows and matching right rows
RIGHT OUTER JOIN	All right rows and matching left rows
FULL OUTER JOIN	All matching and non-matching rows
CROSS JOIN	Every possible combination of rows
SELF JOIN	A table joined to itself

Key Point:
CROSS JOIN is powerful for generating combinations, but it can create very large result sets, so use it carefully.

Oracle Non-Equi Join FAQs with Examples

1. What is a Non-Equi Join in Oracle?

A Non-Equi Join is a join in which tables are joined using operators other than the equality (=) operator.

Common operators used are:

• <
• >
• <=
• >=
• BETWEEN
• LIKE
• <>

A Non-Equi Join is commonly used for range-based matching rather than exact value matching.

Syntax

SELECT column_list
FROM table1 t1
JOIN table2 t2
ON t1.column_name BETWEEN t2.min_value
AND t2.max_value;

2. Why do we use a Non-Equi Join?

A Non-Equi Join is used to:

• Match values within ranges
• Determine salary grades
• Assign tax slabs
• Apply commission percentages
• Categorize data based on intervals
• Perform comparisons using relational operators

3. What is the syntax of a Non-Equi Join?

Example using BETWEEN:

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

4. How does a Non-Equi Join work?

Suppose we have:

EMPLOYEES

EMPLOYEE_ID	EMPLOYEE_NAME	SALARY
101	John	3000
102	David	6000
103	Scott	9000

SALARY_GRADES

GRADE	MIN_SALARY	MAX_SALARY
A	1000	4000
B	4001	7000
C	7001	10000

Query:

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

Output:

EMPLOYEE	SALARY	GRADE
John	3000	A
David	6000	B
Scott	9000	C

5. Why is it called a Non-Equi Join?

Because the join condition does not use the = operator. Instead, it uses operators such as <, >, <=, >=, BETWEEN, LIKE, and <>.

6. What operators can be used in a Non-Equi Join?

Operator	Example
<	e.salary < g.max_salary
>	e.salary > g.min_salary
<=	e.salary <= g.max_salary
>=	e.salary >= g.min_salary
BETWEEN	e.salary BETWEEN g.min_salary AND g.max_salary
LIKE	t1.code LIKE t2.pattern
<>	t1.value <> t2.value

7. Difference between an Equi Join and a Non-Equi Join

Equi Join	Non-Equi Join
Uses =	Uses <, >, BETWEEN, <=, etc.
Matches equal values	Matches ranges or inequalities
Most common join type	Used for range-based matching

Equi Join:

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

Non-Equi Join:

SELECT *
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary
AND g.max_salary;

8. Can a Non-Equi Join use aliases?

Yes. Aliases improve readability.

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

9. Can a Non-Equi Join use WHERE?

Yes.

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

10. Can a Non-Equi Join use GROUP BY?

Yes.

SELECT g.grade,
       COUNT(*) AS employee_count
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary
AND g.max_salary
GROUP BY g.grade;

11. Can a Non-Equi Join use ORDER BY?

Yes.

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

12. Can a Non-Equi Join use multiple conditions?

Yes.

SELECT *
FROM table1 t1
JOIN table2 t2
ON t1.salary BETWEEN t2.min_salary
AND t2.max_salary
AND t1.location = t2.location;

13. Can a Non-Equi Join return duplicate rows?

Yes. If a row matches multiple rows in the joined table, multiple rows may be returned. To avoid this, ensure that ranges do not overlap when only one match is expected.

14. What are common uses of a Non-Equi Join?

• Salary grades
• Tax brackets
• Commission slabs
• Insurance premium ranges
• Age categories
• Discount ranges
• Credit score classifications

15. Real-Time Example – Income Tax Slabs

EMPLOYEE

NAME	SALARY
John	3500
David	6500
Scott	9000

TAX_SLAB

SLAB	MIN	MAX
5%	1000	4000
10%	4001	7000
20%	7001	10000

Query:

SELECT e.employee_name,
       e.salary,
       t.slab
FROM employees e
JOIN tax_slab t
ON e.salary BETWEEN t.min
AND t.max;

16. Can a Non-Equi Join use multiple tables?

Yes.

SELECT e.employee_name,
       g.grade,
       d.department_name
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary
AND g.max_salary
JOIN departments d
ON e.department_id = d.department_id;

17. What are the advantages of a Non-Equi Join?

• Supports range-based matching
• Useful for business rules involving intervals
• Flexible comparison conditions
• Widely used in reporting and analytics
• Can model tax, grading, and pricing systems

18. What are the disadvantages of a Non-Equi Join?

• Can be slower than an Equi Join on large datasets
• May require careful index design for good performance
• Overlapping ranges can produce duplicate or ambiguous matches
• More complex to understand and maintain than simple equality joins

19. Common Errors with Non-Equi Joins

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00918	Column ambiguously defined
ORA-00933	SQL command not properly ended
ORA-01722	Invalid number

20. Non-Equi Join vs Other Joins

Join Type	Description
Equi Join	Joins using =
Non-Equi Join	Joins using range or inequality operators
Inner Join	Returns only matching rows
Left Join	Returns all rows from the left table
Right Join	Returns all rows from the right table
Full Outer Join	Returns matching and non-matching rows
Cross Join	Returns every possible row combination
Self Join	Joins a table to itself

21. Can a Non-Equi Join use the USING clause?

No. The USING clause only supports equality joins on columns with the same name.

Incorrect:

SELECT *
FROM employees
JOIN salary_grades
USING (salary);

Correct:

SELECT *
FROM employees e
JOIN salary_grades g
ON e.salary BETWEEN g.min_salary
AND g.max_salary;

22. Can a Non-Equi Join use NATURAL JOIN?

No. A NATURAL JOIN is based on equality between columns with the same names. For range-based joins, use the ON clause.

23. Interview Scenario

Question: Employees receive a bonus based on salary ranges stored in a BONUS_RULES table. Which join should be used?

Answer: A Non-Equi Join, because the salary must be matched to a range of values rather than an exact value.

Example:

SELECT e.employee_name,
       b.bonus_percent
FROM employees e
JOIN bonus_rules b
ON e.salary BETWEEN b.min_salary
AND b.max_salary;

Key Point:
Non-Equi Joins are best for range-based matching, such as salary grades, tax slabs, and interval-based business rules.

Oracle Equi Join FAQs with Examples

1. What is an Equi Join in Oracle?

An Equi Join is a join in which two tables are joined using the equality (=) operator. It returns rows where the values in the join columns are equal.

Syntax (ANSI JOIN)

SELECT column_list
FROM table1 t1
JOIN table2 t2
ON t1.column_name = t2.column_name;

2. Why do we use an Equi Join?

An Equi Join is used to:

• Retrieve related data from multiple tables
• Match rows based on equal values
• Generate reports
• Maintain normalized database design
• Avoid duplicate storage of related data

3. How does an Equi Join work?

Suppose we have:

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

4. What is the syntax of an Equi Join?

Using ANSI JOIN:

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

Using old Oracle syntax:

SELECT *
FROM employees e,
     departments d
WHERE e.department_id = d.department_id;

5. Why is it called an Equi Join?

It is called an Equi Join because the join condition uses the equal-to (=) operator.

Example:

ON e.department_id = d.department_id

6. What operators are used in an Equi Join?

Only the equality operator:

=

Operators like >, <, >=, <=, and BETWEEN are used in Non-Equi Joins, not Equi Joins.

7. Difference between an Equi Join and a Non-Equi Join

Equi Join	Non-Equi Join
Uses =	Uses <, >, <=, >=, BETWEEN, etc.
Matches equal values	Matches ranges or inequalities
Most commonly used	Used for range-based relationships

Example of a Non-Equi Join:

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

8. Can an Equi Join use multiple columns?

Yes.

Example:

SELECT *
FROM orders o
JOIN shipments s
ON o.order_id = s.order_id
AND o.customer_id = s.customer_id;

Both columns must match.

9. Can an Equi Join 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;

10. Can an Equi Join use WHERE?

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';

11. Can an Equi Join use GROUP BY?

Yes.

Example:

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

12. Can an Equi Join use ORDER BY?

Yes.

Example:

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

13. Can an Equi Join be used with more than two tables?

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;

14. What is the difference between an Equi Join and an Inner Join?

An Equi Join describes the join condition (using =), while an Inner Join describes the join type (returning only matching rows). In practice, most inner joins are equi joins.

Example:

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

This query is both an Inner Join and an Equi Join.

15. Can an Equi Join return duplicate rows?

Yes. If multiple matching rows exist in either table, all matching combinations are returned.

Example: If two employees belong to the same department, both rows are returned.

16. What are the advantages of an Equi Join?

• Simple syntax
• Fast when join columns are indexed
• Retrieves related data efficiently
• Easy to understand
• Most commonly used join type

17. What are the disadvantages of an Equi Join?

• Only works for equality comparisons
• Cannot join ranges
• Requires matching values
• Not suitable for interval or range lookups

18. Common Errors with Equi Join

Error	Cause
ORA-00904	Invalid column name
ORA-00942	Table or view does not exist
ORA-00918	Column ambiguously defined
ORA-00933	SQL command not properly ended

19. Real-Time Example

An HR database stores employee and department information separately.

EMPLOYEES

EMPLOYEE_NAME	DEPARTMENT_ID
John	10
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;

Output:

EMPLOYEE_NAME	DEPARTMENT_NAME
John	Sales
David	HR

20. Equi Join vs Other Joins

Join Type	Description
Equi Join	Joins using the = operator
Non-Equi Join	Joins using range or inequality operators
Inner Join	Returns only matching rows
Left Join	Returns all left rows and matching right rows
Right Join	Returns all right rows and matching left rows
Full Outer Join	Returns matching and non-matching rows
Cross Join	Returns the Cartesian product
Self Join	Joins a table to itself

21. Can an Equi Join use the USING clause?

Yes, if the join columns have the same name in both tables.

Example:

SELECT employee_name,
       department_name
FROM employees
JOIN departments
USING (department_id);

This is equivalent to:

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

22. Can an Equi Join use the old Oracle (+) syntax?

Yes, for outer joins. For example:

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

This performs a left outer join using an equality condition. For modern Oracle development, ANSI JOIN syntax is recommended because it is clearer and easier to maintain.

Key Point:
Equi Join is the most common join pattern in Oracle. It uses equality conditions to match related rows across tables.

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.