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.